TCL commands

TCL commands

(i) COMMIT command

The COMMIT command is used to permanently save any transaction to the database. When any DML commands like INSERT, UPDATE, DELETE commands are used, the changes made by these commands are not permanent. It is marked permanent only after the COMMIT command is given from the SQL prompt. Once the COMMIT command is given, the changes made cannot be rolled back. The COMMIT command is used as

COMMIT;

(ii) ROLLBACK command

The ROLLBACK command restores the database to the last commited state. It is used with SAVEPOINT command to jump to a particular savepoint location. The syntax for the ROLLBACK command is :

ROLL BACK TO save point name;

(iii) SAVEPOINT command

The SAVEPOINT command is used to temporarily save a transaction so that you can rollback to the point whenever required. The different states of our table can be saved at anytime using different names and the rollback to that state can be done using the ROLLBACK command.

SAVEPOINT savepoint_name;

Example showing COMMIT, SAVEPOINT and ROLLBACK in the student table having the following data:

INSERT INTO Student VALUES (107, 'Beena', 'F', 20 , 'Cochin'); COMMIT;

UPDATE Student SET Name = ‘Mini’ WHERE Admno=105; SAVEPOINT A;

INSERT INTO Student VALUES(106, 'Jisha', 'F', 19, 'Delhi'); SAVEPOINT B;

ROLLBACK TO A;