TCL Full Form

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. 

Applications of TCL:-

1. Committing Transactions: TCL statements can be used to commit a transaction, which means to permanently save the changes made during the transaction to the database.
2. Rolling Back Transactions: TCL statements can be used to roll back a transaction, which means to undo the changes made during the transaction and restore the database to its previous state.
3. Setting Transaction Isolation Levels: TCL statements can be used to set the transaction isolation level, which determines the level of concurrency and consistency in the database.
4. Savepoints: TCL statements can be used to set savepoints within a transaction, allowing for partial rollback if needed.
5. Managing Transactions in Stored Procedures: TCL statements can be used in stored procedures to manage transactions within the scope of the procedure.

Overall, TCL is an essential part of SQL and is used extensively in database management systems to control transactions and ensure data consistency. By using TCL statements, database administrators and developers can manage transactions effectively and maintain the integrity of their databases.

The TCL commands are:  

1. COMMIT
2. ROLLBACK
3. SAVEPOINT

1. COMMIT :
This command is used to save the data permanently. 
Whenever we perform any of the DML 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. 

Syntax:

commit; 

2. ROLLBACK :
This command is used to get the data or restore the data to the last savepoint or last committed 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 committed state. 

Syntax:

rollback;

3. SAVEPOINT :
This command is used to save the data at a particular point temporarily, so that whenever needed can be rollback to that particular point. 

Syntax:  

Savepoint A; 

Consider the following Table Student: 

Name	Marks
John	79
Jolly	65
Shuzan	70

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 : 

Name	Marks
John	79
Sherlock	65
Shuzan	70

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: 

Name	Marks
John	79
Sherlock	65
Shuzan	70

Now if ROLLBACK is performed on the above table:  

rollback; 

After Rollback: 

Name	Marks
John	79
Jolly	65
Shuzan	70

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; 

Name	Marks
John	79
Jolly	65
Rossie	70
Jack	95
Zack	76
Bruno	85

Now if we Rollback to Savepoint B:  

Rollback to B; 

The resulting Table will be- 

Name	Marks
John	79
Jolly	65
Rossie	70
Jack	95
Zack	76

Now if we Rollback to Savepoint A:  

Rollback to A; 

The resulting Table will be- 

Name	Marks
John	79
Jolly	65
Rossie	70
Jack	95

So It was all about TCL commands in SQL (transaction control language) with examples.