T-SQL – Will Trigger Complete Before Returning Insert Results?

azure-sql-databasesql-server-2016t-sql

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?

Best Answer

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

Related Question