ROLLBACK TRANSACTION Statement [T-SQL]

Cancels any changes made since a savepoint was established using SAVE TRANSACTION. Changes made prior to the SAVE TRANSACTION are not undone; they are still pending.

Quick Links:

Go to Parameters

Go to Examples

Go to Standards

Go to Permissions

Syntax

ROLLBACK TRANSACTION [ savepoint-name ]

Parameters

(back to top)

savepoint-name – an identifier that was specified on a SAVE TRANSACTION statement within the current transaction. If savepoint-name is omitted, all outstanding changes are rolled back. Any savepoints since the named savepoint are automatically released.

Examples

(back to top)

Example 1 – returns five rows with values 10, 20, and so on. The effect of the delete, but not the prior inserts or update, is undone by the ROLLBACK TRANSACTION statement:

BEGIN
     SELECT row_num INTO #tmp
     FROM sa_rowgenerator( 1, 5 ) 
     SAVE TRANSACTION before_delete
     UPDATE #tmp SET row_num=row_num*10
     DELETE FROM #tmp WHERE row_num >= 3
     ROLLBACK TRANSACTION before_delete
     SELECT * FROM #tmp
END

Standards

(back to top)

SQL–Vendor extension to ISO/ANSI SQL grammar.

Permissions

(back to top)

None, but there must be a corresponding SAVE TRANSACTION within the current transaction.