INF: Rollback from Within Triggers

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

ROLLBACK TRANSACTION behaves differently when used within a trigger than when not within a trigger.

MORE INFORMATION

When not within a trigger, ROLLBACK TRANSACTION must have a matching BEGIN or SAVE point. When not within a trigger, ROLLBACK TRANSACTION backs out uncommitted updates, but does not alter the flow of control of the Transact-SQL batch or stored procedure. To stop processing a stored procedure, an explicit RETURN must be executed in addition to the ROLLBACK.

When within a trigger, it is not necessary to have a matching BEGIN TRANSACTION statement because each SQL statement that is not within an explicit transaction is effectively a one-statement transaction. Nothing at the SQL batch or stored procedure can get "inside" such a one-statement transaction; however, the Transact-SQL statement within a trigger is effectively "inside" the one-statement transaction, and therefore it does make sense to allow an unbalanced rollback to the beginning of the one-statement transaction.

Triggers can also execute as part of a larger transaction. Whether or not they do so, another difference is that a ROLLBACK within a trigger causes the entire SQL batch to be terminated. There is no need for an explicit RETURN to alter the flow of control, nor is there any way to prevent the termination of the batch. The trigger itself does continue to execute after the ROLLBACK is issued; however, when it exits, the rest of the batch is aborted.