How to reference only affected rows in AFTER UPDATE trigger

Posted on

Question :

I have this table:

enter image description here

And I’m trying to create an update trigger which will update last_updated_on = GETDATE() and last_updated_by = SYSTEM_USER columns whenever an update is performed on step_number or step_name column.

I started creating it by

ALTER TRIGGER tr_app_bread_crumbs_afterupdate
ON [dbo].[app_bread_crumbs]
AFTER UPDATE
AS
BEGIN 
    UPDATE [dbo].[app_bread_crumbs]
    SET last_updated_by = SYSTEM_USER,
    last_updated_on = GETDATE()
END

But this will update all rows at once. I’m trying to figure out how to specify to only update last_updated_on and last_updated_by by the trigger for the specific row where updates are being made.

For example if they update:

UPDATE [dbo].[app_bread_crumbs]
    SET step_name = 'DAMAGE' WHERE step_number = 1

Only first row should be updated by the trigger

Answer :

Use the inserted table, which is a special table available inside triggers containing the rows that will be updated/inserted into the table.

ALTER TRIGGER tr_app_bread_crumbs_afterupdate
ON [dbo].[app_bread_crumbs]
AFTER UPDATE
AS
BEGIN 
    UPDATE [dbo].[app_bread_crumbs]
    SET last_updated_by = SYSTEM_USER,
    last_updated_on = GETDATE()
    FROM dbo.app_bread_crumbs abc
    WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.id = abc.id);
END

You need to use the INSERTEDand DELETED pseudo-tables. One way to do this:

ALTER TRIGGER tr_app_bread_crumbs_afterupdate
ON [dbo].[app_bread_crumbs]
AFTER UPDATE
AS
BEGIN
    UPDATE upd
    SET last_updated_by = SYSTEM_USER,
        last_updated_on = GETDATE()
    FROM [dbo].[app_bread_crumbs] AS upd
      JOIN Inserted AS i
        ON i.id = upd.id
      JOIN Deleted AS d
        ON d.id = i.id
    WHERE NOT EXISTS                              -- only when any      
            ( SELECT i.sep_number, i.step_name    -- of the 2 columns
            INTERSECT                             -- changed
              SELECT d.sep_number, d.step_name
            ) ;
END ;

Leave a Reply

Your email address will not be published.