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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s