Sunday, 5 May 2013

SQL SERVER Transaction rollback

In SQL SERVER, modifications to data can be rolled back using BEGIN TRANSACTION statement. In T-SQL programming, after the BEGIN TRANSACTION statement if data has been changed using any DML statement, then all these modifications can be rolled back to the previous consistent state which was present before to the BEGIN TRANSACTION statement. But once you commit the taransaction using COMMIT TRANSACTION, then you can't roll back the transactions. So during any transactions if it throws error then we can get our previous saved state easily using the BEGIN TRANSACTION and ROLLBACK statement.
Here is example of rollback using begin transaction.

USE TESTDATABASE;
GO
CREATE TABLE TESTTable ([value] VARCHAR(10))
GO

BEGIN TRANSACTION
INSERT INTO TESTTable VALUES('MINDFIRE1')
INSERT INTO TESTTable VALUES('MINDFIRE2')

ROLLBACK TRANSACTION
INSERT INTO TESTTable VALUES('MINDFIRE3')
INSERT INTO TESTTable VALUES('MINDFIRE4')

SELECT * FROM TESTTable
 
--Result Values
-------------
MINDFIRE3
MINDFIRE4

Following example shows rollback during error.
USE TESTDATABASE;
GO
CREATE TABLE TESTTable ([value] VARCHAR(10))
GO
SET NOCOUNT ON
BEGIN TRANSACTION
GO
BEGIN TRY
INSERT INTO TESTTable VALUES('MINDFIRE1')
INSERT INTO TESTTable VALUES('MINDFIRE2')
INSERT INTO TESTTable VALUES('MINDFIRE3')
INSERT INTO TESTTable VALUES('MINDFIRE4')

COMMIT TRANSACTION
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
END CATCH
SELECT * FROM TESTTable
GO

Post a Comment