How to reference only affected rows in AFTER UPDATE trigger

7 votes

2 answers

43994 views

sql-server sql-server-2012 t-sql trigger row-modification-time

                          I have this table:

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

Asked by Pawel85 (451 rep)

Oct 20, 2015, 06:32 PM
Last activity: Jan 12, 2017, 07:31 PM

How to reference only affected rows in AFTER UPDATE trigger

Related Questions