Does a rollback inside a INSERT AFTER or UPDATE AFTER trigger rollback the entire transaction
sql-server, sql-server-2008, t-sql, triggers
Solution
Yes it will rollback the entire transaction.
It's all in the docs (see Remarks). Note the comment I've emphasised - that's pretty important I would say!!
If a ROLLBACK TRANSACTION is issued in a trigger:
All data modifications made to that point in the current transaction are rolled back, including any made by the trigger.
The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back. No nested triggers are fired by the execution of these remaining statements.
The statements in the batch after the statement that fired the trigger are not executed.
Problem
Does a rollback inside a INSERT AFTER or an UPDATE AFTER trigger rollback the entire transaction or just the current row that is the reason for trigger, and is it same with Commit ? I tried to check it through my current projects code which uses MSTDC for transactions, and it appears as if though the complete transaction is aborted. If a Rollback in the trigger does rollback the entire transaction, is there a workaround for to restrict it just the current rows. I found a link for sybase on this, but nothing on sql server