Update trigger on column

Question

Update trigger on column

Old Hand

Points: 316

May 1, 2019 at 8:03 am

Hi,
I am experimenting with triggers on SQL 2014. When the column Processed is updated I want to update another column called stage 3 in a different database based on the new value.
I’m almost there but the new value stored in stage 3 is null. This is expected as I’m not sure how to set @Processed to the new value from the update query.

This is the query I run against the table with the trigger:

Update idcard set Processed =1 where recordId=53

Thanks








Idcard table
AFTER UPDATE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @EmployeeID varchar(100)
       DECLARE @Processed INT
 
       SELECT @EmployeeID =     inserted.EmployeeID
       FROM INSERTED
                          
      
       IF UPDATE(Processed)
       BEGIN
                                               
                                    print 'trigger Processed' + CAST(@EmployeeID AS VARCHAR)
                                    update Idd.Main set Stage3 = CASE
                                                                                                                                                                                                                  WHEN @Processed =0 then 1
                                                                                                                                                                                                                  WHEN @Processed =1 then 2
                                                                                                                                                                                                                  END
                                                WHERE EmployeeID=@EmployeeID
     --      
                                               
       END
 
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

It's because you haven't set a value for @Processed. Also, this is going to break if you ever update more than one row with a single UPDATE statement. Do it something like this instead:

UPDATE m
SET Stage3 = Processed+1
FROM Idd.Main m
JOIN Inserted i
ON m.EmployeeID = i.EmployeeID

John

osxman Old Hand Points: 316 More actions · Answer 2

John,

Perfect!!

Thanks for the assistance and pointing out about the multiple update.

Jono