ID Number: Q45581
1.10 1.11 4.20
OS/2
Summary:
ROLLBACK TRANSACTION behaves differently when used within a trigger
than when not within a trigger.
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.
Additional reference words: Triggers, rules, stored procedures