SQL command TCL

 What is TCL?

TCL is a Transaction control language. It is the sublanguage of SQL in which different commands are used to manage the transactions in the database. A transaction is a logical unit of work consisting of one or more SQL statements that are guaranteed to be atomic with respect to recovery. The changes that are made by DML statements like Insert, Delete, Update are managed by the TCL commands. These commands are BEGIN, COMMIT, ROLLBACK, SAVEPOINT. These commands ensure the integrity of data by allowing the statements to be grouped together into logical transactions.

COMMIT Statement:

Commit statement ends the transaction successfully, making the database changes that are done by the DML commands permanent. If we’ll not use the COMMIT command then the changes made by INSERT IN TO, UPDATE or DELETE commands will not be permanent and can be rolled back So for the permanent manipulation COMMIT command will be used.  A new transaction starts after COMMIT with the BEGIN TRANS statement. Its syntax is as follows.

ROLLBACK Statement: The ROLLBACK statement is used for backing out any changes made by the transaction. It is the command that restores the database up to the last COMMIT state. This command work like UNDO Command to undone any transaction in case of error or mistake. A new transaction starts after ROLLBACK with the next transaction initiating statement.

SAVEPOINT Statement:

Savepoint statement provides a label to each transaction which means it provides a point to each transaction by using which you can later on ROLLBACK. As you can see the syntax of ROLLBACK statement needs a savepoint that signifies a point to which you want to undo your transaction or ROLLBACK.

Its syntax is:

Usage Of SAVEPOINT And ROLLBACK:

Let suppose there is the table student that have the column Roll_no, Std_name, Address, Phone_no, etc. In this example I’ll use only two columns Roll_no and Std_name to explain the usage of SAVEPOINT and ROLLBACK. So the table will look like this:

 

Roll_no	Std_name
101	Michal
102	Smith
103	Allen

 

Now we’ll use TCL queries and examine the results.

INSERT IN TO Student (Roll_no, Std_name)

VALUES (104 , ‘Clark’);

COMMIT;

SAVEPOINT STD_4;

INSERT IN TO Student (Roll_no, Std_name)

VALUES (105 , ‘James’);

SAVEPOINT STD_5;

INSERT IN TO Student (Roll_no, Std_name)

VALUES (106 , ‘Ford’);

SAVEPOINT STD_6;

Now we use the SELECT statement to show the data of the table.

SELECT * FROM Student;

The result will be as follows:

Roll_no	Std_name
101	Michal
102	Smith
103	Allen
104	Clark
105	James
106	Ford

Now if we want to ROLLBACK (undo) the insertion of record 106 .we should tell the SAVEPOINT up to which we want to ROLLBACK. As in this case, we want ROLLBACK up to std_5 SAVEPOINT we can do this by this Command.

ROLLBACK TO STD_5;

SELECT * FROM Student;

The resultant table will be:

 

Roll_no	Std_name
101	Michal
102	Smith
103	Allen
104	Clark
105	James

 

If we ROLLBACK to STD_4 SAVEPOINT then

ROLLBACK TO STD_4;

SELECT * FROM Student;

The Student table will be shown like this:

 

Roll_no	Std_name
101	Michal
102	Smith
103	Allen
104	Clark

 

The insertion is Rolled back up the 4^th entrance. The 4^th record can’t be Rolled back because it is committed through the COMMIT statement. So that’s the way how COMMIT, SAVEPOINT And ROLLBACK commands are executed.