ROLLBACK TRANSACTION (Transact-SQL)

Reverts all data modifications made in the current transaction or to a specified savepoint.

Syntax

ROLLBACK TRANSACTION
    [ savepoint_name |
        transaction_name |
        work ]
GO

Parameters

savepoint_name
Specifies that all data modifications made after the savepoint named savepoint_name was reached are undone.

transaction_name
Specifies that all data modifications made since the beginning of the transaction named transaction_name are undone.

work
Specifies that all data modifications made since the beginning of the transaction are undone. This is the default behavior if no savepoint or transaction name is specified.

Description

ROLLBACK TRANSACTION is used within a transaction to undo all changes made to the data.

If a transaction is started with BEGIN TRANSACTION, you can undo all the changes by executing ROLLBACK TRANSACTION. All modifications made from the point BEGIN TRANSACTION was issued are then rolled back.

ROLLBACK TRANSACTION can also be used with savepoints. A savepoint is a point within a transaction to which you can later roll back. The transaction continues to run after the savepoint is rolled back.

When ROLLBACK TRANSACTION is executed, the transaction ends. If ROLLBACK TRANSACTION is issued without a savepoint name or transaction name, it rolls back the entire transaction.

If ROLLBACK TRANSACTION is called without an active BEGIN TRANSACTION statement, the database engine issues a warning and the statement has no effect.

Examples

Example 1: Rolling back an entire transaction

This example demonstrates how to roll back all changes made within a transaction.

BEGIN TRANSACTION;
DELETE FROM Production.Product
    WHERE ProductID = 100;
ROLLBACK TRANSACTION;
GO

Example 2: Rolling back to a savepoint

This example demonstrates how to roll back changes to a specific savepoint.

BEGIN TRANSACTION;
DELETE FROM Production.Product
    WHERE ProductID = 100;
SAVE TRANSACTION DeleteSavepoint;
DELETE FROM Production.Product
    WHERE ProductID = 101;
ROLLBACK TRANSACTION DeleteSavepoint;
GO

In this example, the deletion of ProductID = 100 is rolled back, but the deletion of ProductID = 101 remains. The transaction is still active after the rollback to the savepoint.

See Also