Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
communitycreator

What are TCL commands in SQL?

Buchiredddypalli Koushik

What are TCL commands?

TCL stands for Transaction Control Language and is used to manage information or transactions in a database.

Types of TCL commands

There are three main commands in TCL:

  • Commit
  • Rollback
  • Savepoint
TCL Commands

Commit

The commit command is employed to permanently save the information into the database. This command commits a transaction. When we use DML commands, the changes made by these commands are not permanent. To avoid this, we use the commit command to save it permanently.

Syntax

SQL> commit;

Rollback

The rollback command reinstates the database to the last committed or saved state. It is also used alongside the savepoint command to maneuver to a savepoint in an ongoing transaction.

Syntax

SQL> rollback to s1;

Here, s1 is the name of the savepoint.

Savepoint

Savepoint is used to temporarily save the data so that we can roll back to it whenever we need to. It adds a savepoint within a transaction.

Syntax

SQL> savepoint s1;

s1 is the name of the savepoint.

Code

# create a table 

create table cars(model int ,car_name varchar(20),manufacture_year int);

# Inserting the values into the table

insert into cars (model,car_name,manufacture_year) values(1542,"Safari",2021);
insert into cars(model,car_name,manufacture_year) values(2562,"Benz",2020);
insert into cars (model,car_name,manufacture_year) values(1098,"BMW",2021);
select *from cars;
start transaction;

# Here we have created a savepoint

savepoint s1;
# after creating a savepoint the data gets stored till the point where we have done.

delete from cars where model=1098;
insert into cars values(2547,"Jaguar",2021),(8745,"Audi",2020),(1878,"Nexa",2020);
rollback to s1;

# when we rollback it gets us back to the last saved point.

select *from cars;
commit;

#commit is used to save the data permenantly.

Explanation

  • In the code above, we create a table with the name cars.

  • After creating the table, we insert the values into it.

  • We add a savepoint to temporarily save the values.

  • Then, we use the rollback command to go back to a point where we last saved. In other words, we can go back to the last savepoint.

  • Finally, we use commit to permanently save the data.

RELATED TAGS

sql
communitycreator

CONTRIBUTOR

Buchiredddypalli Koushik
RELATED COURSES

View all Courses

Keep Exploring