snippetsqlModerate
How to reference only affected rows in AFTER UPDATE trigger
Viewed 0 times
afterrowsreferencetriggerupdateaffectedhowonly
Problem
I have this table:
And I'm trying to create an update trigger which will update
I started creating it by
But this will update all rows at once. I'm trying to figure out how to specify to only update
For example if they update:
Only first row should be updated by the trigger
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()
ENDBut 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 = 1Only first row should be updated by the trigger
Solution
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);
ENDCode Snippets
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);
ENDContext
StackExchange Database Administrators Q#118648, answer score: 10
Revisions (0)
No revisions yet.