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. 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



Department Table



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.



RUN



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.



RUN



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.




RUN


EXAMPLE 4: SIMPLE NESTED TRANSACTIONS



RUN



EXAMPLE 5: NESTED TRANSACTIONS



RUN



EXAMPLE 6: NESTED TRANSACTIONS



RUN



EXAMPLE 7: TWO SEPARATE TRANSACTION



RUN



EXAMPLE 8: TRANSACTION WITHIN A STORED PROCEDURE

STORED PROCEDURE 1



STORED PROCEDURE 2



RUN



EXAMPLE 9: TRANSACTION WITHIN A STORED PROCEDURE

STORED PROCEDURE 1



STORED PROCEDURE 2



RUN



NOTE

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