TCL command

Transaction Control Language(TCL) commands are used to manage transactions in database.These are used to manage the changes made by DML statements. It also allows statements to be grouped together into logical transactions.

These commands are to keep a check on other commands and their affect on the database. These commands can annul changes made by other commands by rolling back to original state. It can also make changes permanent.

---

Commit command

Commit command is used to permanently save any transaaction into database.

Following is Commit command's syntax,

commit;

Rollback command

This command restores the database to last commited state. It is also use with savepoint command to jump to a savepoint in a transaction.

Following is Rollback command's syntax,

rollback to savepoint-name;

Savepoint command

savepoint command is used to temporarily save a transaction so that you can rollback to that point whenever necessary.

Following is savepoint command's syntax,

savepoint savepoint-name;

Example of Savepoint and Rollback

Following is the class table,

ID	NAME
1	ankit
2	anuj
4	garima

Lets use some SQL queries on the above table and see the results.

INSERT into class values(5,'Rahul');
commit;
UPDATE class set name='abhijit' where id='5';
savepoint A;
INSERT into class values(6,'Chris');
savepoint B;
INSERT into class values(7,'Bravo');
savepoint C;
SELECT * from class;

The resultant table will look like,

ID	NAME
1	ankit
2	anuj
4	garima
5	abhijit
6	chris
7	bravo

Now rollback to savepoint B

rollback to B;
SELECT * from class;

The resultant table will look like

ID	NAME
1	ankit
2	anuj
4	garima
5	abhijit
6	chris

Now rollback to savepoint A

rollback to A;
SELECT * from class;

The result table will look like

ID	NAME
1	ankit
2	anuj
4	garima
5	abhijit