Transaction Control Language (TCL)

0
1013

Transaction Control Language

SQL stands for structured query language which is used to create, modify, retrieve, and store the data in the relational database.

Introduction to Transaction Control Language

Transaction Control Language(TCL) commands are manage transactions in the database. These are managed the changes made to the data in a table by DML statements. It also allows statements to be grouped commonly into logical transactions. Read on Resume Building Guide for Data Scientists

There are some commands of TCL in SQL

  • Commit
  • Savepoint
  • Rollback

With the help of these commands in SQL, we can easily control the database structure.

COMMIT: Commit command is used to permanently store or save any transaction into the database.

ROLLBACK: This command restores the database to last committed point It is also used with savepoint command to jump to a savepoint with its name in a transaction.

SAVEPOINT: Savepoint command is used to temporarily save a transaction with a savepoint name that you can rollback to that point whenever necessary.

Commit Command:-

COMMIT command is used to permanently store or save any transaction into the database.

When we use any Data manipulation language commands like INSERT, UPDATE, or DELETE, the modification made by these commands are not permanent until the session is closed, the changes made by these commands can be rolled back.

To avoid that, we use the COMMIT command to identify the changes as permanent.

Syntax of Commit Command

Commit;

For example:-

Create a table student and commit the session.

Query:-
Create table students(roll_no number(10),name varchar2(20));
commit;

Output:-
Commit complete;

Savepoint Command:-

SAVEPOINT command is used to tentatively save a transaction so that you can rollback to that point whenever needed.

Using this command we can name the various states of our data in any table and then rollback to that state using the ROLLBACK command whenever needed.

Syntax of savepoint Command:-

Savepoint  [name of savepint];

For example:-

Update the name of the student name to ‘Rishikesh’ whose student id is 101 and save it temporarily.

STUDENT_ID STUDENT_NAME         BRANCH_ID  STUDENT_NO

       101 Abhishek            civil          123456

       102 Sahil               mech            78912

Query:-
Update students 
Set student_name =’Rishikesh’
Where stuednt_id=101;
Savepoint A;

Output:-
Savepoint completed;

Rollback Command:-

This command returns the database to the last committed state. It is also used with the SAVEPOINT command to jump to a savepoint with its name in a current transaction.

If we have used the UPDATE command to make some modifications into the database, and realize that those modifications were not required, then we can use the ROLLBACK command to rollback those modifications, if they were not committed using the COMMIT command.

Syntax of savepoint Command:-

Rollback;

Rollback  [name of savepint];

For example:-

Update the name of the student name to ‘Rishikesh’ whose student id is 101 and save it temporarily and then rollback the changes.

Query:-
Update students 
Set student_name =’Rishikesh’
Where stuednt_id=101;
Savepoint A;
Rollback A;


Output:-
rollback completed;

Here we rollback all the updated information which we update recently by using the name of savepoint and rollback command.

Conclusion

Now you can do the easy transactions on the database after reading this blog the example also helpful for you to create a procedure. Want to learn SQL? Visit our Website for more information.

LEAVE A REPLY

Please enter your comment!
Please enter your name here