TCL Commands in SQL (commit, rollback and Savepoint commands in SQL)

TCL commands in SQL:

TCL : Transaction Control Language.

TCL commands are used to manage the transactions in database. These are used to manage the DML commands i.e Insert, Update, Delete.  These TCL commands are acts like second level authentication. Suppose example, without commit system won't update the modified data into database. It just applicable for the particular session only.

Below are the TCL commands.

1. Commit

2. Rollback

3. Savepoint

1. COMMIT: 

Syntax:  COMMIT                             

COMMIT command is used to save the transactions(Insert, Update and Delete)  permanently into the database.

 

So if we run any INSERT, UPDATE and DELETE command in SQL, System won't save/commit explicitly. So the changes applicable for the particular session only.

So to avoid such issues, We have to run COMMIT command to save the transaction/update data permanently.

2. ROLLBACK:

Syntax:  ROLLBACK                        

ROLLBACK command is used to restores the database last committed state.

If we have used INSERT or UPDATE or DELETE command to change/add the something and later realized that changes not required Then we have to use the ROLLBACK command.

3. SAVEPOINT:

Syntax : SAVEPOINT SavepointName            

SAVEPONT command is used to save the transaction temporarily by setting a point. So that we can rollback to that particular SAVEPONT. 

With examples:

SQL> UPDATE TABLE EMPLOYEE SET NAME='KRISH1' where NAME='KRISH';

SQL> SAVEPOINT A1;

SQL> UPDATE TABLE EMPLOYEE SET NAME='CHANDU' where NAME=CHANDU12';

SQL> SAVEPOINT A2;

We have updated Two rows. If we want to rollback up to second save point then we have to use below syntax.

SQL> ROLLBACK TO A2; 

So the above query will rollback up to the A2 SAVEPOINT.  So using SAVEPOINT, We can set a value and rollback our changes.

                                                        Thank You