How to reference only affected rows in AFTER UPDATE trigger

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 1

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

• Andriy M: Since they want to log the user and the time only when specific columns are updated, perhaps it would be a good idea to suggest the UPDATE() function as well.
• Aaron Bertrand: ...or a join to inserted making sure those values have changed, because UPDATE() returns true if a column is mentioned, regardless of whether the value actually changed.

Answer 2

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 ;