CODEWITHSUNDEEP
sql-server-2008
user2135970
user2135970

Reputation: 815

Using and UPDATE Trigger to Modify the Row Being Updated

I have an employee table (em) that contains, among other things, a floor Id (fl_id) and room Id (rm_id). In certain circumstances (when em_loc_cnt = 0) I want to set the em record's fl_id and rm_id to null. Below is my code so far.

I am not sure how to refer to the fl_id & rm_id in the commented line. Will I run into issues because this trigger is being called as a result of the em record being updated and I am updating that same record in the trigger?

Suggestions?

IF EXISTS (SELECT * FROM [sysobjects] WHERE [name] = 'em_upd_self_serv_t' AND [type] = 'TR')
  BEGIN
    DROP TRIGGER [dbo].[em_upd_self_serv_t]
 END
GO

CREATE TRIGGER [dbo].[em_upd_self_serv_t]
  ON [dbo].[em]
  AFTER UPDATE
AS
BEGIN
  DECLARE @em_id VARCHAR(35),
          @em_loc_cnt INT;

  SET @em_id = (SELECT em_id FROM inserted);
  SET @em_loc_cnt = (SELECT COUNT(*) FROM emlocs WHERE em_id = @em_id);

  IF (@em_loc_cnt = 0) 
    BEGIN
      -- I want to set the fl_id and the rm_id to NULL
    END
END;

Upvotes: 1

Views: 46

Answers (1)

marc_s
marc_s

Reputation: 754368

Your fundamental flaw is that you seem to expect the trigger to be fired once per row - this is NOT the case in SQL Server. Instead, the trigger fires once per statement, and the pseudo table Inserted might contain multiple rows.

Given that that table might contain multiple rows - which one do you expect will be selected here??

SET @em_id = (SELECT em_id FROM inserted);

It's undefined - you might get the values from arbitrary rows in Inserted.

You need to rewrite your entire trigger with the knowledge the Inserted WILL contain multiple rows! You need to work with set-based operations - don't expect just a single row in Inserted !

You need to change your code to something like this:

IF EXISTS (SELECT * FROM sys.triggers WHERE [name] = 'em_upd_self_serv_t')
    DROP TRIGGER [dbo].[em_upd_self_serv_t]
GO

CREATE TRIGGER [dbo].[em_upd_self_serv_t]
  ON [dbo].[em]
  AFTER UPDATE
AS
   UPDATE dbo.em
   SET fl_id = NULL, rm_id = NULL
   FROM Inserted i
   WHERE em_id = i.em_id
     AND NOT EXISTS (SELECT * FROM dbo.emlocs WHERE em_id = i.em_id)

Upvotes: 2

Related Questions