In this post I’m going to detail with examples how triggers behave with transactions…
How Do Triggers Behave With Transactions?
To demo this let’s insert a record into a table that has an after insert trigger from a transaction and call rollback from the trigger. If you’ve read my nested transactions post you’ll know that any ROLLBACK nested or not will end all transactions and that nested transactions are really a lie.
If we then run an insert into the table from a transaction and then call commit on the transaction will it be saved or will the trigger roll it back?
If we check our table you’ll see there were no results inserted….
So in short a trigger executes in the context of the calling transaction and a rollback in a trigger will rollback the calling transaction.
Do Errors in Triggers Prevent Data Being Saved?
What if instead of the ROLLBACK in our trigger we have a bug that causes an error…
If we then run our insert again will it save?
Nope again we didn’t make it to our commit and the transaction has been automatically rolled back.
How about if our insert wasn’t in an explicit transaction?
Same error and still no records in TriggerTest table.
In short any rollback or run time error in a trigger will prevent the underlying data from being saved.
Do Triggers Fire at The End Of Transactions Or As Changes Are Made?
Given a transaction with multiple statements does the trigger fire as each statement is called or only when the transaction ends? Let’s check…
So, triggers fire on each statement as they are run inside the transaction.