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.
There are some commands of TCL in SQL
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 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
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 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];
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;
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 [name of savepint];
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.
Now you can do the easy transactions on the database after reading this blog the example also helpful for you to create a procedure.