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
Best Answer
Use the
inserted
table, which is a special table available inside triggers containing the rows that will be updated/inserted into the table.