TCL stands for Transaction Control Languages. These commands are used for maintaining consistency of the database and for the management of transactions made by the DML commands.
A Transaction is a set of SQL statements that are executed on the data stored in DBMS. Whenever any transaction is made these transactions are temporarily happen in database.So to make the changes permanent, we use TCL commands.
The TCL commands are:
1. COMMIT :
This command is used to save the data permanently.
Whenever we perform any of the DDL command like -INSERT, DELETE or UPDATE, these can be rollback if the data is not stored permanently. So in order to be at the safer side COMMIT command is used.
2. ROLLBACK :
This command is used to get the data or restore the data to the last savepoint or last commited state. If due to some reasons the data inserted, deleted or updated is not correct, you can rollback the data to a particular savepoint or if savepoint is not done, then to the last commited state.
3. SAVEPOINT :
This command is used to save the data at a particular point tempoararily, so that whenever needed can be rollback to that particular point.
Consider the following Table Student:
UPDATE STUDENT SET NAME = ‘Sherlock’ WHERE NAME = ‘Jolly’; COMMIT; ROLLBACK;
By using this command you can update the record and save it permanently by using COMMIT command.
Now after COMMIT :
If commit was not performed then the changes made by the update command can be rollback.
Now if no COMMIT is performed.
UPDATE STUDENT SET NAME = ‘Sherlock’ WHERE STUDENT_NAME = ‘Jolly’;
After update command the table will be:
Now if ROLLBACK is performed on the above table:
If on the above table savepoint is performed:
INSERT into STUDENT VALUES ('Jack', 95); Commit; UPDATE NAME SET NAME= ‘Rossie’ WHERE marks= 70; SAVEPOINT A; INSERT INTO STUDENT VALUES (‘Zack’, 76); Savepoint B; INSERT INTO STUDENT VALUES (‘Bruno’, 85); Savepoint C; SELECT * FROM STUDENT;
Now if we Rollback to Savepoint B:
Rollback to B;
The resulting Table will be-
Now if we Rollback to Savepoint A:
Rollback to A;
The resulting Table will be-
So It was all about TCL commands in SQL (transaction control language) with examples.
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- SQL | DDL, DQL, DML, DCL and TCL Commands
- SQL | DDL, DML, TCL and DCL
- Difference between DML and TCL
- Difference between DDL and TCL
- LFU Full Form
- VPN Full Form
- DOS Full Form
- SAS Full Form
- OSI Model Full Form in Computer Networking
- HDMi Full Form
- LCD Full Form
- ZIP Full Form
- API Full Form
- RTC Full Form
- VLAN Full Form
- WPA Full Form
- DQL Full Form
- VGA Full Form
- SGML Full Form
- DHCP Full Form
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.