Transactions within Triggers

I’ve just spotted quite an interesting question of the day on SQL Server Central from Friday.  http://www.sqlservercentral.com/questions/T-SQL/98251/

It’s to do with how transactions behave inside a trigger.  I selected 4 as my answer as I thought the transaction only existed within the trigger itself and did not affect the calling transaction.  As the trigger is an “after” trigger, I assumed it would write the data in the second insert statement, throw the error and then continue to write the data in the third and fourth insert statements.  It turns out this is not the case and a rollback statement in a trigger will roll back the open transaction (in this case the second insert statement) and then kill the whole batch so no further statements are executed.

Advertisements