Rollbacks and Commits in Stored Procedures and Triggers
Executing a ROLLBACK TRANSACTION or COMMIT TRANSACTION Transact-SQL statement inside a stored procedure or trigger is possible, but doing so may cause errors.
In Stored Procedures
If @@TRANCOUNT has a different value when a stored procedure finishes than it had when the procedure was executed, an informational error (266) occurs. This can happen in two ways:
- A stored procedure is called with an @@TRANCOUNT of 1 or greater and the stored procedure executes a ROLLBACK TRANSACTION statement. @@TRANCOUNT decrements to 0 and causes an error 266 when the stored procedure completes.
- A stored procedure is called with an @@TRANCOUNT of 1 or greater and the stored procedure executes a COMMIT TRANSACTION statement. @@TRANCOUNT decrements by 1 and causes an error 266 when the stored procedure completes. However, if BEGIN TRANSACTION is executed after the COMMIT TRANSACTION, the error does not occur.
In Triggers
A trigger operates as if there were an outstanding transaction in effect when the trigger is executed. This is true whether the statement firing the trigger is in an implicit or explicit transaction.
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.
When a trigger executes, an implicit transaction is started. If a trigger completes execution with an @@TRANCOUNT = 0, an error 3609 occurs and the batch is terminated. For this reason, inside of triggers it is advisable to avoid using ROLLBACK TRANSACTION, which resets @@TRANCOUNT to 0, and COMMIT TRANSACTION, which may decrement @@TRANCOUNT to 0. Issuing a BEGIN TRANSACTION statement after a rollback will stop the error from being raised, but this could cause problems with application logic.
It is important to understand that a BEGIN TRANSACTION statement issued in the trigger is actually beginning a nested transaction. In this situation, executing a COMMIT TRANSACTION statement will apply only to the nested transaction. Because a nested BEGIN TRANSACTION statement is ignored when rolling back nested transactions, ROLLBACK TRANSACTION executed in the trigger rolls back past any BEGIN TRANSACTION statements issued by the trigger itself. ROLLBACK rolls back to the outermost transaction and sets @@TRANCOUNT to 0.
When using ROLLBACK TRANSACTION in a trigger, be aware of the following behavior:
- 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.
- 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. However, 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.
Instead of using ROLLBACK TRANSACTION, the SAVE TRANSACTION statement can be used to execute a partial rollback in a trigger.
See Also
Concepts
Other Resources
@@TRANCOUNT (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
ROLLBACK WORK (Transact-SQL)
SAVE TRANSACTION (Transact-SQL)