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
- There exist no other triggers anywhere.
- Client uses EntityFramework Core so time in question is after
- The PrimaryKey of the
AuditEntriestable is the
The question is: are the
AuditEntries guaranteed to exist at this time or is this a race I need to handle?
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?