rollback

Description

Rolls back a user-defined transaction to the named savepoint in the transaction or to the beginning of the transaction.

Syntax

rollback [tran | transaction | work]
	[transaction_name | savepoint_name]

Parameters

tran | transaction | work

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.

transaction_name

is the name assigned to the outermost transaction. It must conform to the rules for identifiers.

savepoint_name

is the name assigned to the savepoint in the save transaction statement. The name must conform to the rules for identifiers.

Examples

Example 1

Rolls back the transaction:

begin transaction
delete from publishers where pub_id = "9906"
rollback transaction

Usage


Restrictions


Rolling back an entire transaction


Rolling back to a savepoint

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.


Rollbacks within triggers and stored procedures

Standards

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.

Permissions

No permission is required to use rollback.

See also

Commands begin transaction, commit, create trigger, save transaction