Enable trigger after another trigger is fired

Question

I am new to triggers and I am in a position where I have to use them. I have an azure db with two triggers on a table, one on insert, one on update.

Insert: fires when a record is inserted to a table. Copies one column to another:

CREATE TRIGGER [dbo].[tr_Set_Adjusted_StartDateTime]
ON [dbo].[Work]
AFTER INSERT
AS
BEGIN
    UPDATE dbo.Work
    SET [ActualStartDateTime] = [work].[StartDateTime]
    FROM inserted
    WHERE dbo.Work.WorkUID = inserted.WorkUID;
END

Update trigger (fires when the record gets updated):

CREATE TRIGGER [dbo].[tr_Set_Actual_EndDateTime]
ON [dbo].[Work]
AFTER UPDATE
AS
   IF((Select [ActualEndDateTime] from Deleted) is null)
   BEGIN
       UPDATE dbo.Work
       SET [ActualEndDateTime] = GETUTCDATE()
       FROM deleted
       WHERE dbo.Work.WorkUID = deleted.WorkUID;
   END

The second trigger should only execute once: the first time the record is updated. Because the stored procedure that inserts the record doesn't populate all the columns.

The second trigger didn't originally have the IF statement. But there is an admin site that can manipulate the db and set off the update trigger.

The IF statement is now automatically firing the update trigger right away.

Is there a way to disable the update trigger if it is executed BY another trigger? Or only enable the update trigger after the record has been created?

Take note - inserted and deleted can contain multiple rows (or no rows). So an IF check is probably broken because you could be in a situation where for some rows the test would be true and for other rows, the test would be false. — Damien_The_Unbeliever

M.Ali M.Ali · Accepted Answer · 2014-09-25T20:59:05

If you have an After Update trigger on a table, it will fire each time there is an update on that table. You cannot tell trigger to Only Fire once.

But there is a way around to it. You can add a field in that table , a BIT field and SET its value to 1 in your trigger, Never manipulate this field in directly. And inside your UPDATE trigger

UPDATE w
SET [ActualEndDateTime] = GETUTCDATE()
   ,[Tr_Update] = 1
FROM deleted d INNER JOIN dbo.Work w ON w.WorkUID = d.WorkUID
WHERE [Tr_Update] = 0
 AND [ActualEndDateTime] IS NULL

On a side note you are checking if the user hasn't put any date you want to add Current Datetime to [ActualEndDateTime] column. and since this is an after update trigger if you just execute the above statement with WHERE clause [ActualEndDateTime] IS NULL,

It would update [ActualEndDateTime] to current datetime when a row is updated for the first time and next time because [ActualEndDateTime] field would not be null it would simply filter it out anyway.

UPDATE w
SET [ActualEndDateTime] = GETUTCDATE()
FROM deleted d INNER JOIN dbo.Work w ON w.WorkUID = d.WorkUID
WHERE [ActualEndDateTime] IS NULL

Enable trigger after another trigger is fired

1 Answers