Nested Transactions in SQL Server

In our previous article we explained everything about the Transactions with examples. Please refer SQL Transactions article to understand the concept of transactions. In this article we will show you, What are the Nested Transactions in SQL Server? and How to create them with example. We will also discuss about the Transaction Save Points in SQL Server.

From the below screenshot you can see, we created a new table to demonstrate the Nested Transactions.

Nested Transactions in SQL Server Example 1

In this example we will use the @@TRANCOUNT to display the number of transactions occurred at each layer. This is the simple example without any problem

SQL CODE

OUTPUT

Let me show you the practical example.

In this example we are using two INSERT Statements. One is inside the Main transaction, and another one in Nested Transaction.

SQL CODE

OUTPUT

Nested Transactions in SQL Server Example 2

In this example we are checking what will happen if we use the ROLLBACK TRANSACTION as the nested transaction.

SQL CODE

OUTPUT

From the above screenshot, you can see it is throwing an error message for the Last COMMIT TRANSACTION TRAN1 statement. Because, ROLLBACK will rollback all the statements.

From the below screenshot you can observe that, after the Rollback is executed there are no transactions running.

In this example we are using two INSERT Statements. One is inside the Main transaction, and another one in Nested Transaction.

SQL CODE

OUTPUT

From the below screenshot you can see that there is no insertion happened.

Nested Transactions in SQL Server Example 3

In this example we are checking what will happen if we use the ROLLBACK TRANSACTION as the outer transaction.

SQL CODE

OUTPUT

Let me show you one practical example.

SQL CODE

OUTPUT

This will rollback everything inside the first transaction, and second transaction.

SQL Server Transactions Save Point Example 1

In this example we will show you the working function of the Transaction save points. In the below code snippet we have SAVE TRAN and this command will save the data up to that point. For example, if there is any disaster happens after that point, or any rollback command executed doesn’t delete data before the save point.

SQL CODE

OUTPUT

Let me show you the practical example.

SQL CODE

OUTPUT

Thank You For Visiting Our Blog