SQL Transaction Control Language (TCL)

This article explains in brief about Transaction control language which provides accessibility to control the changes in database, for example TCL controls the execution of DML operation (insert/update/delete) in database.It also illustrates to how to control the DML operation using Transaction control statement.

In order to understand the concept of SQL Transaction Control Language (TCL) follow the points given below:

1. What is Sql transaction?

• Transactions are like event whenever something happen in database, like updating or deleting then sql server holds that occurrence in Transaction lock.

• Normally without TCL we don’t have opportunity to control any changes that happen in database and record can permanently store in database.

1. Begin Transaction

1. Commit

1. Rollback

1. Usages of Transaction:

• Use to control the DML operation

• Provide accessibility to When to commit the changes

• To undo the changes

• To set save point for changes

• Using transaction we can control the different state of tables.

1. Begin Transaction:

• Whenever events are happen, Begin transaction tells the sql server, the beginning of a transaction before attempting a modification in data.

• Data is not save permanently until commit occur

1. Commit Transaction:

• It permanently saves the event (insert/update/delete) in database Or it commit the changes in database

• Commit transaction require corresponding Begin transaction.

1. Rollback Transaction:

• Use to undo the changes in data and also it rollback the transaction if any error occur

• If commit occur first then you cannot rollback the transaction

Example: In following example I am showing the transaction for insert statement for table Salary.

Let’s look on salary table first to check the data:

Original State:

Intermediate State:

• In above screen salary table is in state with no data.

• To insert a new row table will be go in intermediate state that can be change to new state if transaction are committed otherwise it can roll back to pervious or original state.

Now we can control the DML operation, if you want to save above state, use commit under DML operation. Or if you want roll back to pervious state use rollback.

To rollback to previous state :

Commit (it save data permanently to table)

Thus, we learnt in brief about Transaction control language which provides accessibility to control the changes in database and to control the DML operation using Transaction control statement.