TCL Command:




The Transaction Control Language commands are used to track the effects of other commands on the database and are also used to control the transactional processing in a database. The changes made using the transaction control command are permanent and cannot be altered. The primary keywords or commands of transaction control language are SAVEPOINT, ROLLBACK, and COMMIT.




Commit Command

To save any transaction permanently into the database the commit command is used. The syntax of commit command is as follows:

Commit;

EXAMPLE

Suppose you have deleted some records from a table using the delete command and then used the commit command all the changes of the transaction will be saved permanently. Let you deleted a record from the given table employee:

Dept_No Dept_Name Emp_No Emp_Name
10 Management E01 John Abraham
20 Management E02 Tim
30 Finance E04 Ali
40 Finance E11 Faddy
50 IT E25 Kate

Use the following query to delete a record:

DELETE from employee WHERE DEPT_NO = 40;

Commit;

This will delete a row from the table and will result as follows:

Dept_No Dept_Name Emp_No Emp_Name
10 Management E01 John Abraham
20 Management E02 Tim
30 Finance E04 Ali
50 IT E25 Kate

 

Rollback Command

 If the user has deleted some data or records and also used the commit statement then the changes are permanently stored. To restore or to undo the last committed state or transaction the rollback command is used. The syntax of rollback command is as follows:

Rollback;

EXAMPLE

Suppose you have a table named employee:

Dept_No Dept_Name Emp_No Emp_Name
10 Management E01 John Abraham
20 Management E02 Tim
30 Finance E04 Ali
40 Finance E11 Faddy
50 IT E25 Kate




If you use delete statement to delete a record and then use the rollback command then the delete command will not have any effect and no changes will be made. In short the record is deleted but using the rollback command it is recovered or restored. Use the following query to understand the use of rollback command:

DELETE from employee where DEPT_NO = 40;

Rollback;

The result produced by this query will be as follows:

Dept_No Dept_Name Emp_No Emp_Name
10 Management E01 John Abraham
20 Management E02 Tim
30 Finance E04 Ali
40 Finance E11 Faddy
50 IT E25 Kate

It can be seen that no changes are made the table is as it was.

 

Savepoint Command

If the user wants to save a transaction from a specific point and does not want to roll back to the whole of the transaction then the savepoint command is used. This command will save the transaction permanently and the user can roll back to a certain point when necessary. The syntax of savepoint command is given below:

Savepoint savepoint-name;

If the user wants to roll back to a savepoint then the syntax for this will be as follows:

Rollback to savepoint-name;

EXAMPLE

Suppose you are deleting a number of records from a table then you can make save points so that you can roll back to the data from an appropriate location or point. Consider the table employee and delete some records but before deleting each record you have to specify a savepoint so that you can roll back that specific point:

Dept_No Dept_Name Emp_No Emp_Name
10 Management E01 John Abraham
20 Management E02 Tim
30 Finance E04 Ali
40 Finance E11 Faddy
50 IT E25 Kate

The query for this is given below:

Savepoint s1;
DELETE from employee where DEPT_NO = 10;
Savepoint s2;
DELETE from employee where DEPT_NO = 20;
Savepoint s3;
DELETE from employee where DEPT_NO = 30;




Now you have successfully deleted three records from the table employee. Suppose you want to restore the last two deletions or records then you will use the rollback command with the savepoint name as s2.

As after the savepoint s2 there are two more records therefore, the last two records will be recovered. Use the following query to understand this:

Rollback to s2;

The above query will undo the last two deleted records.