In Focus

Various Types of Transactions in SQL Server

This article explains how to use the transactions and various way of writing the transactions in SQL Server.

This article explains how to use the transactions and various way of writing the transactions in SQL Server. Please see the examples to understand better. I have taken an example to explain this in details.

First you need to create two tables. The tables are as follows.

Employee Table

Transactions1.jpg

Department Table

Transactions2.jpg

EXAMPLE 1: SIMPLE TRANSACTION

In the following example, I am inserting the data to the employee table. I am using TRAN1 as my transaction. I am committing the transition in a try block and rolling back the same transaction in a catch block. Here there is no error so the transaction is committed.

Transactions3.jpg

RUN

Transactions4.jpg

EXAMPLE 2: ROLL BACK TRANSACTION

Here I am using an error statement after an insert statement, so the commit transaction will not be executed and it will go to the catch block. In the catch block, the rollback statement will be executed and the entire transaction will be rolled back.

Transactions5.jpg

RUN

Transactions6.jpg

EXAMPLE 3: ROLL BACK TRANSACTION

Here I am using an error statement after the commit transaction. The records will be inserted but the Stored Procedure will give you an error. Once you commit the transaction, you cannot roll it back.

Transactions7.jpg


RUN

Transactions8.jpg
EXAMPLE 4: SIMPLE NESTED TRANSACTIONS

Transactions9.jpg

RUN

Transactions10.jpg

EXAMPLE 5: NESTED TRANSACTIONS

Transactions11.jpg

RUN

Transactions12.jpg

EXAMPLE 6: NESTED TRANSACTIONS

Transactions13.jpg

RUN

Transactions14.jpg

EXAMPLE 7: TWO SEPARATE TRANSACTION

Transactions15.jpg

RUN

Transactions16.jpg

EXAMPLE 8: TRANSACTION WITHIN A STORED PROCEDURE

STORED PROCEDURE 1

Transactions17.jpg

STORED PROCEDURE 2

Transactions18.jpg

RUN

Transactions19.jpg

EXAMPLE 9: TRANSACTION WITHIN A STORED PROCEDURE

STORED PROCEDURE 1

Transactions20.jpg

STORED PROCEDURE 2

Transactions21.jpg

RUN

Transactions22.jpg

NOTE

Hope you will enjoy this article. Please provide the feedback.