Transaction Controls

How To Use Transaction Controls In SQL

Introduction:
Today, in this article, you will learn what is Transaction Controls and how to use Transaction Controls in SQL.

These commands are only used with the Insert, Update and Delete commands in SQL. We cannot include Create or Delete tables in these commands because they are committed itself.

Transaction Commands allow four basic commands:

• Commit Command

• Rollback Command

• Savepoint Command

• Set Transaction Command

Here, we have an Employee table for showing the result:

The Commit Command: Commit Command is used to save data on each change. It is applied by an operation to the database. It saves data from the last commit.

Syntax:

The basic syntax for Commit Command is as follows:

commit;

Example:

Step 1: First we write the query for Commit Command.

Now select and execute this query.

Step 2: And the output is:

The Rollback Command: This command is used to run previous data in the Transaction.

Syntax:

The basic syntax for Rollback Command is as follows:

rollback;

Example:

Step 1: First we write the query for Rollback Command.

Now select and execute this query.

Step 2: And the output is:

The Savepoint Command: With the help of this command, we can find the particular transaction without searching the whole transaction.

Syntax:

The basic syntax for Savepoint Command is as follows:

savepoint savepoint_name;

The Rollback command is used to run previous data in the Transactions. It’s used only for the creation of a Savepoint.

Syntax:

The basic syntax for rollback to a Savepoint Command is as follows:

rollback to savepoint_name;

Example:

Step 1: First we write the query for Savepoint Command.

Now select and execute this query.

Step 2: And the output is:

The Set Transaction Command: This command is used to initiate the database Transaction.

Syntax:

The basic syntax for Set Transaction Command is as follows:

set transaction [read write| read only];

Summary:
Thus, we learned that Transaction Controls works with the insert, update and delete commands and also learn it's used in SQL.