Nested transactions in SQL
Here is the SQL Statement which uses Nested Transaction. Is anybody guess what is the output of the below statement.
CREATE TABLE #Dept (Id INT IDENTITY (1,1), DepartName NVARCHAR(50))
BEGIN TRANSACTION OuterTran
INSERT INTO #Dept VALUES (‘IT‘)
BEGIN TRAN InnerTran
INSERT INTO #Dept VALUES (‘HR‘)
ROLLBACK TRAN InnerTran
ROLLBACK TRANSACTION OuterTran
SELECT * FROM #Dept
First I thought the output will be empty table. But the actual result is
· Committing/Roll backing inner transactions is ignored by the SQL Server Database Engine.
· The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction.
· If the outer transaction is committed, the inner nested transactions are also committed.
· If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.