Articles → SQL SERVER → Transactions In SQL Server

Transactions In SQL Server

In this article, we will discuss transactions in the SQL server.

What Are Transactions?

Consider an example, where you want to update 2 tables in one go. Our requirement is, either both the tables should get updated or no table should be updated.

In this scenario, the transaction comes into the picture. Transaction in an SQL server is a set of statements executed as a single unit. If any of the statements fail, then the entire transaction will be rollbacked or else committed.

Syntax

Begin Transaction -- commit command will make the changes permanent in database
Commit Transaction -- rollback command will cancel the whole transaction
RollBack TRANSACTION

Example

Consider three tables "T1", "T2" and "T3" with the following schema.

Picture showing the table schema of three tables

Click to Enlarge

First, execute the following statements.

Begin Transaction

Insert Into T1 values('Test1')
Insert Into T2 values('Test1')

Commit Transaction

IF @@ERROR > 0

RollBack TRANSACTION

In the above statement, we are trying to insert the value "Test1" in both the tables i.e., "T1" and "T2". As the insert statements are valid, so the transaction will be committed.

Once you execute the above statement and see the records in tables "T1" and "T2" then you could see the committed transaction.

Picture showing the data inserted into table1 and table2

Click to Enlarge

Now, let us execute the second transaction.

Begin Transaction

Insert Into T1 values('Test2')
Insert Into T3 values('Test2')

Commit Transaction

IF @@ERROR > 0

RollBack TRANSACTION

In the above transaction, the second insert will fail because we are trying to insert the string value in the date field.

Picture showing the error message when tries to enter string value in the date field

Click to Enlarge

Now, if you see the tables "T1" and "T3", you will observe that value "Test2" is not getting inserted in any of the tables.

Picture showing that values is not inserted in any of the table because the transaction fails

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Tuesday, September 12, 2017

Query/Feedback

Your Email Id

Subject

Query/Feedback	Characters remaining 250