How to revert back a change or data entered in a statement.

Problem: How to revert back a change or data entered in a statement.

Solution: ROLLBACK TRANSACTION.

In SQL Server, a single batch of statement is called as transaction. Transactions are of three types:

•     Auto commit Transactions
•     Explicit Transactions (begin, commit and rollback specified on a transaction)
•    Implicit Transactions (commit and rollback completes a transaction)

If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

A daily life example to understand what transaction means and rollback means in SQL Server.

Transaction 1: consider a person X going to bank attempting to deposit 100$ in bank (successful transaction)

Transaction 2: on the next day he tries to withdraw 150$ from the bank (As he has only 100$ the transaction fails and then rolls back to go to previous state.)

Example:

Let’s create some basic transactional statements and to see how we can implement rollback transcation.

IF OBJECT_ID('tempdb..#Test') IS NOT NULL

    DROP TABLE #Test

CREATE TABLE #Test

(

    Col1   TINYINT,

    Col2   TINYINT,

    Col3   TINYINT,

    Col4   TINYINT

)

GO

INSERT INTO #Test(Col1,Col2,Col3,Col4)

select  1, 2, 3,4

UNION

select  2, 3,4 ,5

--marks the transaction

BEGIN TRANSACTION

--these values are inserted Explicitly specific to a transaction scope.

INSERT INTO #Test(Col1,Col2,Col3,Col4)

select  3,4,5,6

UNION

select  4,5,6,7

-- verifying to see wehter we have values inserted or not.

SELECT * FROM #TEST

--Rolling Back Transaction.

ROLLBACK TRANSACTION

--verifying to see whether the trasaction has been rolled back or not.

SELECT * FROM #TEST

OUTPUT: