6

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

10

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
  • 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. – Andriy M Oct 20 '15 at 19:09
  • 2
    ...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. – Aaron Bertrand Oct 20 '15 at 19:24
5

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.