What are TCL commands in SQL with examples?

In my previous article I have given so many examples of SQL as well as the tutorials of SQL. In this article I would like to give you the information about the TCL commands in SQL with examples. The TCL stands for transactional control Language and there are some SQL statements which controls the database transactions. The SQL transaction is unit of work performed against the database. If you are building any kind of logic then you require transactional statements which controls the transactions. The transactional Control Language is used to maintain the integrity as well as consistency in the databases.

Why TCL?

The Transaction Control Language used to change the data the DML operations. The alteration made by the DML commands such as UPDATE, INSERT or DELETE is not permanent and these changes can be canceled before the present session gets closed. There are so many times we require to change the transactions or unknowingly you perform the transaction and you require to have control on it then you can use the transactional control language.

What are TCL commands in SQL with examples?

There is always question in mind that what are TCL commands in SQL . There are following 3 commands in SQL :

1.Commit

2.Rollback

3.Savepoint

1. COMMIT

This command is used to make a transaction permanent in a database. So it can be said that commit command saves the work done as it ends the current transaction by making permanent changes during the transaction. The syntax for this command is as below.

COMMIT;

For instance, we want to update the location of an employee in the table “EMPLOYEE”. Sample EMPLOYEE table is given below:

EMP_ID	EMP_NAME	EMP_LOC
1000	Amit	Pune
2000	Raju	mumbai
3000	Ram	Chennai

Let us update the EMP_ LOC for Amit as below:

Query:

UPDATE EMPLOYEE SET EMP_ LOC = 'stockholm' WHERE EMP_NAME= 'Amit';
COMMIT;

The update transaction is completed with the commit command as above and the usage of the above statements will update the location of the employee ‘Amit’ and the change will be saved in the database permanently.

The updated table is as shown below:

EMP_ID	EMP_NAME	EMP_LOC
1000	Amit	stockholm
2000	Raju	mumbai
3000	Ram	chennai

2. ROLLBACK

This command is used to restore the database to its original state since the last command that was committed. The syntax of the Rollback command is as below:

ROLLBACK;

Also, the ROLLBACK command is used along with savepoint command to leap to a save point in a transaction. The syntax for the same is as below:

ROLLBACK TO <savepoint_name>;

Let us take the example of the EMPLOYEE table as cited above. Let us consider that we have updated EMP_LOC for Raju to Bangalore later and realize that the update was done mistakenly as below. Then we can restore the EMP_LOC for ‘Raju’ to Hyderabad again by using the Rollback command as below.

Query:

UPDATE EMPLOYEE SET EMP_LOC= 'Bangalore' WHERE EMP_NAME = 'Amit';
ROLLBACK;

After the wrong update the table is as below:

EMP_ID	EMP_NAME	EMP_LOC
1000	Amit	Stockholm
2000	Raju	Bangalore
3000	Ram	Hyderabad

After the Rollback is performed, the location for Amit is restored to the last committed state as shown below.

EMP_ID	EMP_NAME	EMP_LOC
1000	Amit	Hyderabad
2000	Raju	Bangalore
3000	Ram	Hyderabad

3. SAVEPOINT

This command is used to save the transaction temporarily. So the users can rollback to the required point of the transaction. The syntax for using this command is as below:

SAVEPOINT savepoint_name;

Let us take the example of a table “ORDERS” with columns as ORDER_ID and ITEM_NAME.

ORDER_ID	ITEM_NAME
100	Laptop
200	Mobile

Let us insert the below values to the ORDERS table below and perform the updates using savepoint.

Query:

INSERT INTO ORDERS VALUES ('300' , 'CELL PHONE');
COMMIT;
UPDATE ORDERS SET ITEM_NAME = 'SMART PHONE' WHERE ORDER_ID= '300';
SAVEPOINT A;
INSERT INTO ORDERS VALUES ('400' , 'mixer');
SAVEPOINT B;
Now the ORDERS table will be as below:

Now the ORDERS table will be as below:

ORDER_ID	ITEM_NAME
100	LAPTOP
290	MOBILE
300	SMART PHONE
400	MIXER

Now we can use the SAVEPOINT command to Rollback the transaction. Let us Rollback the transaction to savepoint A.

Query:

ROLLBACK TO A;

The ORDERS table will be as below:

ORDER_ID	ITEM_NAME
100	LAPTOP
200	MOBILE
300	SMART PHONE

The TCL commands in SQL provides the privilege to rollback the transaction if the data is updated in the tables by mistake. It performs a permanent change to the database by locking the data using the commit command. Also with the help of savepoint command, users can save the transactions temporarily and if required, can also perform rollback using the savepoint.

TCL commands in SQL helps in maintaining the consistency and integrity of the data. The database can be restored to the last committed state as well as modifications made can be saved permanently with the help of TCL. The developers should have a keen understanding of TCL to build a robust system. I hope this article finds you very informative and helpful to you guys. If you like this article or if you have any issues with the same kindly comment in comments section.