Rollbacks in Stored Procedures and Triggers

If @@TRANCOUNT has a different value when a stored procedure finishes than it had when the procedure was executed, an informational error 266 is generated. This error is not generated by the same condition in triggers.

A 266 error is generated when a stored procedure is called with an @@TRANCOUNT of 1 or greater and the procedure executes a ROLLBACK TRANSACTION or ROLLBACK WORK statement. This is because ROLLBACK rolls back all outstanding transactions and decrements @@TRANCOUNT to 0, which is a lower value than it had when the procedure was called.

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 that were 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.
• None of the statements in the batch after the statement that fired the trigger are executed.
• A ROLLBACK in a trigger closes and deallocates all cursors that were declared and opened in the batch containing the statement that fired the trigger. This includes cursors declared and opened in stored procedures called by the batch that fired the trigger. Cursors declared in a batch prior to the batch that fired the trigger are only closed, except that STATIC or INSENSITIVE cursors are left open if:
  • CURSOR_CLOSE_ON_COMMIT is set OFF.
  • The static cursor is either synchronous, or a fully populated asynchronous cursor.

A trigger always operates as if there were an outstanding transaction in effect when the trigger is executed. This is definitely true if the statement firing the trigger is in an implicit or explicit transaction. It is also true in autocommit mode. When a statement begins executing in autocommit mode, there is an implied BEGIN TRANSACTION to allow the recovery of all modifications generated by the statement if it encounters an error. This implied transaction has no effect on the other statements in the batch because it is either committed or rolled back when the statement completes. This implied transaction is still in effect, however, when a trigger is called.

This means that any time a BEGIN TRANSACTION statement is issued in the trigger, it is actually beginning a nested transaction. Because a nested BEGIN TRANSACTION statement is ignored when rolling back nested transactions, ROLLBACK TRANSACTION issued in the trigger always rolls back past any BEGIN TRANSACTION statements issued by the trigger itself. ROLLBACK rolls back to the outermost BEGIN TRANSACTION.

You must use the SAVE TRANSACTION statement to do a partial rollback in a trigger, even if it is always called in autocommit mode. This is illustrated by the following trigger:

CREATE TRIGGER TestTrig ON TestTab FOR UPDATE AS

SAVE TRANSACTION MyName

INSERT INTO TestAudit

   SELECT * FROM inserted

IF (@@error <> 0)

BEGIN

  ROLLBACK TRANSACTION MyName

END

This also affects COMMIT TRANSACTION statements that follow a BEGIN TRANSACTION statement in a trigger. Because BEGIN TRANSACTION starts a nested transaction, a subsequent COMMIT statement applies only to the nested transaction. If a ROLLBACK TRANSACTION statement is executed after COMMIT, ROLLBACK rolls back everything to the outermost BEGIN TRANSACTION. This is illustrated by the following trigger:

CREATE TRIGGER TestTrig ON TestTab FOR UPDATE AS

BEGIN TRANSACTION

INSERT INTO TrigTarget

   SELECT * FROM inserted

COMMIT TRANSACTION

ROLLBACK TRANSACTION

This trigger will never insert into the TrigTarget table. BEGIN TRANSACTION always starts a nested transaction. COMMIT TRANSACTION commits only the nested transaction, while the following ROLLBACK TRANSACTION rolls everything back to the outermost BEGIN TRANSACTION.

See Also