Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Thank you very much. Glad to hear it. –  Aushin Mar 1 '11 at 15:13
    
@Aushin I've revised my answer a touch with some important notes. –  squillman Mar 1 '11 at 15:15
    
@squillman Thank you for the added detail. I wish I could accept both answers as THE answer. –  Aushin Mar 1 '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 1 '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 1 '11 at 15:21
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.