Saturday, January 29, 2011

How do we rollback the table data in SQL Server

Actually rollbacks are automatic. MS SQL Server is fully 
ACID compliant. 

ROLLBACK { TRAN | TRANSACTION } 
     [ transaction_name | @tran_name_variable
     | savepoint_name | @savepoint_variable ] 
[ ; ]

Arguments:
transaction_name 
Is the name assigned to the transaction on BEGIN 
TRANSACTION. transaction_name must conform to the rules for 
identifiers, but only the first 32 characters of the 
transaction name are used. When nesting transactions, 
transaction_name must be the name from the outermost BEGIN 
TRANSACTION statement.

@ 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 
Is savepoint_name from a SAVE TRANSACTION statement. 
savepoint_name must conform to the rules for identifiers. 
Use savepoint_name when a conditional rollback should 
affect only part of the transaction.

@ 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.

 Remarks 
ROLLBACK TRANSACTION erases all data modifications made 
from the start of the transaction or to a savepoint. It 
also frees resources held by the transaction.

ROLLBACK TRANSACTION without a savepoint_name or 
transaction_name rolls back to the beginning of the 
transaction. When nesting transactions, this same statement 
rolls back all inner transactions to the outermost BEGIN 
TRANSACTION statement. In both cases, ROLLBACK TRANSACTION 
decrements the @@TRANCOUNT system function to 0. ROLLBACK 
TRANSACTION savepoint_name does not decrement @@TRANCOUNT.

A ROLLBACK TRANSACTION statement specifying a 
savepoint_name releases any locks acquired beyond the 
savepoint, with the exception of escalations and 
conversions. These locks are not released, and they are not 
converted back to their previous lock mode.

ROLLBACK TRANSACTION cannot reference a savepoint_name in 
distributed transactions started either explicitly with 
BEGIN DISTRIBUTED TRANSACTION or escalated from a local 
transaction.

A transaction cannot be rolled back after a COMMIT 
TRANSACTION statement is executed.

Within a transaction, duplicate savepoint names are 
allowed, but a ROLLBACK TRANSACTION using the duplicate 
savepoint name rolls back only to the most recent SAVE 
TRANSACTION using that savepoint name.

In stored procedures, ROLLBACK TRANSACTION statements 
without a savepoint_name or transaction_name roll back all 
statements to the outermost BEGIN TRANSACTION. A ROLLBACK 
TRANSACTION statement in a stored procedure that causes 
@@TRANCOUNT to have a different value when the stored 
procedure completes than the @@TRANCOUNT value when the 
stored procedure was called produces an informational 
message. This message does not affect subsequent processing.
answer2:-
begin transaction
save transcation t
delete from tablename where id=2
select * from tablename
------------------------------------------------------
the value id=2 will be deleted in the tablename
-------------------------------------------------------
rollback transcation t
select * from tablename
---------------------------------------------------------
if u give rollback the deleted values will be seen again 
Above code spelling are wrong on save Transaction
correct code are below which is executeable 

begin transaction
save transaction t
delete from tablename where id=2
select * from tablename
------------------------------------------------------
the value id=2 will be deleted in the tablename
-------------------------------------------------------
rollback transaction t
select * from tablename
           
            

No comments:

Post a Comment