Rolls back a user-defined transaction to the named savepoint in the transaction or to the beginning of the transaction.
rollback [tran | transaction | work] [transaction_name | savepoint_name]
specifies that you want to roll back the transaction or the work. If you specify tran, transaction, or work, you can also specify the transaction_name or the savepoint_name.
is the name assigned to the outermost transaction. It must conform to the rules for identifiers.
is the name assigned to the savepoint in the save transaction statement. The name must conform to the rules for identifiers.
Rolls back the transaction:
begin transaction delete from publishers where pub_id = "9906" rollback transaction
rollback transaction without a transaction_name or savepoint_name rolls back a user-defined transaction to the beginning of the outermost transaction.
rollback transaction transaction_name rolls back a user-defined transaction to the beginning of the named transaction. Though you can nest transactions, you can roll back only the outermost transaction.
rollback transaction savepoint_name rolls a user-defined transaction back to the matching save transaction savepoint_name.
If no transaction is currently active, the commit or rollback statement has no effect.
The rollback command must appear within a transaction. You cannot roll back a transaction after commit has been entered.
rollback without a savepoint name cancels an entire transaction. All the transaction’s statements or procedures are undone.
If no savepoint_name or transaction_name is given with the rollback command, the transaction is rolled back to the first begin transaction in the batch. This also includes transactions that were started with an implicit begin transaction using the chained transaction mode.
To cancel part of a transaction, use rollback with a savepoint_name. A savepoint is a marker set within a transaction by the user with the command save transaction. All statements or procedures between the savepoint and the rollback are undone.
After a transaction is rolled back to a savepoint, it can proceed to completion (executing any SQL statements after that rollback) using commit, or it can be canceled altogether using rollback without a savepoint. There is no limit on the number of savepoints within a transaction.
In triggers or stored procedures, rollback statements without transaction or savepoint names roll back all statements to the first explicit or implicit begin transaction in the batch that called the procedure or fired the trigger.
When a trigger contains a rollback command without a savepoint name, the rollback aborts the entire batch. Any statements in the batch following the rollback are not executed.
A remote procedure call (RPC) is executed independently from any transaction in which it is included. In a standard transaction (that is, not using Open Client™ DB-Library two-phase commit), commands executed via an RPC by a remote server are not rolled back with rollback and do not depend on commit to be executed.
For complete information on using transaction management statements and on the effects of rollback on stored procedures, triggers, and batches, see the Transact-SQL User’s Guide.
ANSI SQL – Compliance level: Entry-level compliant.
Transact-SQL extensions The rollback transaction and rollback tran forms of the statement and the use of a transaction name.
rollback permission defaults to “public.” No permission is required to use it.
Commands begin transaction, commit, create trigger, save transaction
