ROLLBACK TRANSACTION Statement

Rolls back a user-specified transaction to the last savepoint inside a transaction or to the beginning of a transaction.

Syntax

ROLLBACK TRANsaction [transaction_name | savepoint_name]

where

transaction_name

Is the name assigned to the transaction. The transaction_name must conform to the rules for identifiers. Use transaction names only on the outermost pair of nested BEGIN... COMMIT or BEGIN...ROLLBACK statements.

savepoint_name

Is the name assigned to the savepoint. The savepoint_name must conform to the rules for identifiers. Use savepoint names only when a conditional rollback should affect only part of the transaction.

Remarks

ROLLBACK TRANSACTION, without a savepoint_name or transaction_name, will roll back to the beginning of the transaction. When nesting transactions, a ROLLBACK TRANSACTION statement, without a transaction_name or savepoint_name, always rolls back statements to the outermost BEGIN TRANSACTION statement and cancels the transaction. All the transaction's statements or procedures are undone. A transaction cannot be rolled back after a COMMIT TRANSACTION statement is executed.

Within a transaction, duplicate savepoint_names are allowed, but when used with ROLLBACK TRANSACTION, only the first instance of a savepoint_name is used.

In triggers or stored procedures, ROLLBACK TRANSACTION statements, without a savepoint_name or transaction_name, roll back all statements to the first BEGIN TRANSACTION in the batch that called the procedure or fired the trigger. A ROLLBACK TRANSACTION statement in a stored procedure or trigger that rolls back past more than one BEGIN TRANSACTION statement produces an error message indicating the number of nesting levels affected. This message (level 16) does not affect subsequent processing.

The rollback also affects all data modification statements issued from within the trigger. The statements executed as a result of the trigger firing and the transaction that attempted the data modification against the table are treated as an atomic unit by the rollback. Remember that the entire transaction is terminated if the rollback is issued from within a trigger. Also note that upon entering the trigger, the global variable @@TRANCOUNT is incremented, in spite of the lack of a BEGIN TRANSACTION statement. (The system treats a trigger as an implied transaction.)

ROLLBACK TRANSACTION statements in stored procedures do not affect subsequent statements in the batch that called the procedure; subsequent statements in the batch are executed. ROLLBACK TRANSACTION statements in triggers "undo" the modification attempted (that fired the trigger), and subsequent statements in the batch are not executed.

A ROLLBACK TRANSACTION statement does not produce any messages to the user. If warnings are needed in stored procedures, use the RAISERROR statement or the PRINT statement. The RAISERROR statement is the preferred method for sending errors.