MS SQL Server 2012 - DBA Articles

BEGIN ... ROLLBACK Transaction Process Flow

ROLLBACK Transaction is an explicit or implicit transaction to the beginning of the transaction, or to a SAVEPOINT inside the transaction. ROLLBACK Transaction is used to erase all data modifications made from the start of the transaction or to a SAVEPOINT.

Syntax

-- Syntax --

ROLLBACK { TRAN | TRANSACTION }

[ transaction_name | @tran_name_variable

| savepoint_name | @savepoint_variable ]

[ ; ]

Transaction_Name

Transaction_Name is the name assigned to the transaction on BEGIN Transaction. Transaction_Name must conform to the rules for identifiers.

@tran_name_variable

@tran_name_variable is the name of a user-defined variable containing a valid transaction name. The variable must be declared with a char, varchar, nchar, or nvarchar data type.

Savepoint_Name

Savepoint_Name is savepoint_name from a SAVE TRANSACTION statement. Savepoint_Name must conform to the rules of identifiers.

@savepoint_variable

@savepoint_variable is name of a user-defined variable containing a valid savepoint name. The variable must be declared with a char, varchar, nchar, or nvarchar data type.

The following example is used to delete particular Employee record and recover from deleted record information.

-- Example 68 --

SELECT * FROM EMP

BEGIN TRANSACTION MyTransaction

DELETE FROM EMP WHERE Emp_Code = 501;

ROLLBACK TRANSACTION MyTransaction

SELECT * FROM EMP

Query Output Screen

* * * * *

Your Comment To AUTHOR