1

I have the following trigger for Auditing.

CREATE OR ALTER TRIGGER dbo.TR_MyTrigger ON dbo.Cust
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EntityState AS SMALLINT;
    SET @EntityState = (CASE
                        WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED) THEN 3 -- Updated
                        WHEN EXISTS(SELECT * FROM INSERTED) THEN 4 -- Inserted
                        WHEN EXISTS(SELECT * FROM DELETED) THEN 2 -- Deleted
                    ELSE NULL END)
    IF @EntityState = 4
    BEGIN
        UPDATE Cust 
        SET Created = GETDATE(), Updated = GETDATE() 
        FROM INSERTED I
        WHERE Cust.BinId = I.BinId
    END
    ELSE IF @EntityState = 3
    BEGIN
        UPDATE Cust 
        SET Updated = GETDATE() 
        FROM INSERTED I
        WHERE Cust.BinId = I.BinId
    END
    IF @EntityState <> 2
    BEGIN
        INSERT INTO AuditEntries (SomeColumns..) 
        SELECT SomeColumns..
        FROM INSERTED I;
    END
    ELSE
    BEGIN
        INSERT INTO AuditEntries (SomeColumns..) 
        SELECT SomeColumns..
        FROM DELETED I;
    END
END;

When the client(API Server) call writing to the table Cust is complete, the next call inserts userIds to the AuditEntries table.

Extra info:

  • There exist no other triggers anywhere.
  • Client uses EntityFramework Core so time in question is after SaveChangesAsync() returns.
  • The PrimaryKey of the AuditEntries table is the ROWVERSION of dbo.Cust

The question is: are the AuditEntries guaranteed to exist at this time or is this a race I need to handle?

Other research:

sql-server-trigger-execution

An answer to: Are Sql Triggers synchronous or asynchronous

UPDATE: The above trigger does not enter a loop via direct recursion on any update operations due to the default T-Sql configuratoin. See: Recursive Triggers

Found an SO this am that answers my original question: Does a trigger in a transaction only fire when the transaction is committed?

1

DELTE the UPDATE from your trigger

CREATE OR ALTER TRIGGER dbo.TR_MyTrigger ON dbo.Cust
    AFTER INSERT, DELETE
AS
BEGIN

When you delete the UPDATE trigger Case 4 and 3 wouldn't fire the trigger again.

So only when you INSERT or DELETE, the trigger would run, and update Cust or insert into AuditEntries

With this AFTER UPDATE TRiGGER you would create a loop.

so you should write another triigger only for UPDATES, if you need one.

To answer your question

The trigger would run after the INSER or DELETE of dbo.Cust and will run completely before starting another command, that is because you define your trigger as FOR EACH STATEMENT

As Example

  1. INSERT INTO dbo.Cust VALUE (1,),(2,),(3,),(4,),(5,)
  2. TRIGGER dbo.TR_MyTrigger
  3. INSERT INTO dbo.Cust VALUE (6,),(7,),(8,),(9,),(10,)
  4. TRIGGER dbo.TR_MyTrigger
  5. DELETE dbo.Cust ...
  6. TRIGGER dbo.TR_MyTrigger#

Every step is completed before the next

| improve this answer | |
  • Hmm, I definitely need to handle that loop. And thank you. If I remove the offending portion of setting updated / created. Can you speak to the original question? – ttugates Jun 10 at 0:24
  • The trigger would run after the INSER or DELETE of dbo.Cust and will run completly before starting another command., that is because you define your trigger FOR EACH STATEMENT – nbk Jun 10 at 0:41
  • @ngk, I spent some time looking particularly into the update loop and can't verify it exists. I updated Question with code I would expect to repro but doesn't seem to. I am not clear on what you mean by "because you define your trigger FOR EACH STATEMENT" - But I need to know if inserting into dbo.Cust -> which causes trigger to run(at least once-hopefully no more) will complete its insertion record into AuditEntries table before the client's dbo.Cust inserting code invocation returns.. Your example doesn't explicitly answer this part that is the key question I have. Thank you again – ttugates Jun 10 at 1:52
  • Previous comment was in response to @nbk. I misspelled and its too late to edit. – ttugates Jun 10 at 2:01
  • 1
    AFTER triggers are not triggerd by DML performed by the trigger itself, by default. This is controlled by the RECURSIVE_TRIGGERS database option, which is off by default. docs.microsoft.com/en-us/sql/relational-databases/triggers/… – David Browne - Microsoft Jun 10 at 14:02

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.