Transactions Control Language

Transaction is nothing but a unit of work. We can control these transactions using the following statements.

1. ROLLBACK Statement

2. COMMIT Statement

3. SAVE TRAN [SACTIONS]

ROLLBACK Statement: This statement is used to cancel a particular performed transaction. To perform this statement in Sql Server we have to follow any one of the below 2 approaches.

Approach 1: SET IMPLICIT_TRANSACTIONS ON: This approach is only to cancel a single recently performed operation.

Example:

SET IMPLICIT_TRANSACTIONS ON
SELECT * FROM EMP
DELETE * FROM EMP
SELECT * FROM EMP
ROLLBACK
SELECT * FROM EMP

Approach 2: Explicit Transactions: To approach is to cancel recently performed multiple operations.

Syntax:

BEGIN TRAN
----------
GO
---------
GO
--------

ROLLBACK TRAN

GO is query separator

Example:

BEGIN TRAN
INSERT INTO DEPT VALUES (50,'TRA','AUS')
GO
UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11
GO
DELETE FROM STUDENT WHERE SNO=101

Select the entire transaction and press F5 for one time

ROLLBACK TRAN

The ROLLBACK TRAN statement cancels INSERT on Dept, UPDATE on EMP and DELETE on student tables.

COMMIT Statement: This statement makes a transaction permanent. It is not possible to rollback the committed transaction.

Example:

SELECT * FROM EMP
DELETE * FROM EMP
SELECT * FROM EMP
COMMIT
SELECT * FROM EMP

The COMMIT statement deletes the data from EMP permanently. It is not possible to ROLLBACK the delete operation.

Example:

BEGIN TRAN
INSERT INTO DEPT VALUES (50,'TRA','AUS')
GO
UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11
GO
DELETE FROM STUDENT WHERE SNO=101

Select the entire transaction and press F5 for one time

COMMIT TRAN

The above COMMIT TRAN makes all three transactions permanent. We cannot ROLLBACK the transactions.

SAVE TRANS Statement: This statement is used to COMMIT/ROLLBACK a particular performed transaction from the set of transactions. It is associated with alphabets in order to save the transaction.

BEGIN TRAN
SAVE TRAN A
INSERT INTO DEPT VALUES (50,'TRA','AUS')
SAVE TRAN B
UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11
SAVE TRAN C
DELETE FROM STUDENT WHERE SNO=101

ROLLBACK TRAN C (The delete operation will be cancelled)

COMMIT TRAN B (The update operation performed permanently we cannot rollback)

Transactions Control Language