MSSQL 2005 Trigger Behavior

Question

I'm wondering how a trigger on a table behaves in SQL Server in response to an uncommitted transaction affecting that table if the trigger is specified to fire AFTER UPDATE.

If the transaction hasn't been committed on the UPDATE to the table, will the trigger fire anyway? Or does it wait (as I hope it does) for the transaction to commit before firing?

squillman · Answer 1 · 2011-03-01T15:21:36.580

1

It is somewhat how you hope. If the transaction does not commit then an AFTER UPDATE trigger will not fire, but it's only true for the entire transaction if you check the value of @@Error after your statements and rollback the transaction.

Consider this:

CREATE TABLE Table1 (SomeNumber int NOT NULL)
CREATE TABLE Table2 (SomeNumber int)

CREATE TRIGGER UpdAfterTrigger
   ON  Table1
   AFTER UPDATE
AS 
BEGIN
   INSERT Table2 SELECT 1
END

INSERT Table1 (SomeNumber) SELECT 1

BEGIN TRANSACTION

UPDATE Table1 SET SomeNumber=1
UPDATE Table1 SET SomeNumber=1
UPDATE Table1 SET SomeNumber=1
UPDATE Table1 SET SomeNumber=1
UPDATE Table1 SET SomeNumber=NULL

IF (@@error <> 0)
BEGIN
  ROLLBACK TRANSACTION 
END
ELSE
BEGIN
    COMMIT TRANSACTION
END

The trigger will not fire in this case since the transaction is getting rolled back because of the error when inserting NULL into a NOT NULL column.

If you don't check for @@Error <> 0 and just blindly commit the transaction then the trigger in this example will fire for each of the 4 good UPDATE statements in the batch.

edited Mar 01 '11 at 15:21

answered Mar 01 '11 at 15:08

squillman

37,883
12
92
146

@Aushin I've revised my answer a touch with some important notes. – squillman Mar 01 '11 at 15:15
@squillman Thank you for the added detail. I wish I could accept both answers as THE answer. – Aushin Mar 01 '11 at 15:18
@Aushin I've often wished that I could do that :) Just wanted to make sure you saw my edit! – squillman Mar 01 '11 at 15:19
It's my understanding that the trigger *will* fire for those first four UPDATE statements. It's just that you're potentially rolling back the work it's done if an error is detected. – db2 Mar 01 '11 at 15:21
@db2 I see what you're saying, and you may be technically right. I validated the code above on an instance here and Table2 indeed was empty after running. BUT, it could be that the rollback pulled out the changes made by the trigger as you say. – squillman Mar 01 '11 at 15:24

score 1 · Accepted Answer · answered Mar 01 '11 at 15:13

The trigger will run immediately after the UPDATE, and will be considered part of the active transaction.

Consider a situation where the trigger updates/cleans some data in the table - you wouldn't be able to reference this corrected data until after attempting to commit the transaction if the trigger didn't fire immediately.

MSSQL 2005 Trigger Behavior

2 Answers2