Yes, you can rollback a transaction from a trigger. But just because you can doesn”t mean you should. In the contrary â€“ if you do not catch exceptions on the SQL Server side, using a ROLLBACK statement inside triggers put you in serious risk.
Understanding T-SQL Error Handling
If you asked me what part of SQL Server is the toughest to understand, I would say error handling. Things like memo (the structure used by the optimizer) or latches (some kind of light-weight locks) are way easier, because they applied to some rules.
But error handling seems to be totally messed up. For example, the first thing you probably learned about exception handling was that there are different types of errors:
1. With statement terminating errors execution resumes at the next statement.
2. When a scope terminating error occurs, execution resumes at the next statement after the one that called the procedure that was terminated.
Great, but why missing or superfluous parameter to stored procedure with parameters is a statement terminating errors, while passing any parameter to a parameterless stored procedure is a scope terminating one?
Errors and Transactions
To add more confusion, most errors are statement terminating errors, which means that a statement that fails even when enclosed in a transaction does not automatically rolled the transaction back, only the statement itself. Fortunately, XACT_ABORT session settings can be used to convert statement terminating errors into batch terminating errors. Those will abort the whole batch and roll back any transaction enclosing the statement that failed.
Now let me show you triggers can silently messed up your data.
Triggers abort the whole batch
To see why you must be extra careful when using a ROLLBACK statement inside triggers, create this simple table and an even simpler trigger:
First, we will try insert three rows into this table:
While the second row was inserted, the transaction ended in the trigger and the whole batch has been aborted. This is why only the first row is in the table:
OK, maybe that should happened. So, let me truncate this table and we will go to a second part of this demo:
Now, we will insert those three rows inside an explicitly started and committed transaction:
Please note that the error message is exactly the same as previously (because the whole batch was aborted, the COMMIT statement was not executed, so there error 3902 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION was not thrown):
(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure trT1, Line 10
Value 5 is not allowed
Msg 3609, Level 16, State 1, Line 3
The transaction ended in the trigger. The batch has been aborted.
But this time no rows was inserted:
However, if all of those statement are executed in separate batches, only the two first inserts will be rollback, the last one will stay and the additional error 3609 will be thrown:
So, this way the last row will be inserted even if the whole transaction is rolled back:
In other words, by executing exactly the same insert statements, we inserted the first row, then no rows and finally only the last row.
1. Statement level error (the most common ones) do not rollback transaction.
2. If you rollback a transaction from a trigger the whole batch will be automatically aborted. That is the case even if you do not throw any error from a trigger.
3. If your application sends queries in separate batches, the trigger effect on explicitly started transaction is questionable, to say the least.
Hope this help