As the name suggests, nested transaction is one that is nested within a transaction. Let’s illustrate this with an example.
Let’s create a table:
CREATE TABLE PERSON ( ID INT IDENTITY(1,1), FirstName VARCHAR(25), LastName VARCHAR(25), Gender CHAR(1) )
SELECT * FROM PERSON
As you can see, we have a table Person with no records.
Let’s initialize a transaction and view its count
BEGIN TRANSACTION -- 1st Transaction SELECT @@TRANCOUNT AS TrxCount
Now, insert a record into the table
INSERT INTO PERSON VALUES ('Bill', 'Meyer', 'M')
SELECT * FROM PERSON
Let’s initialize a second transaction and view its count:
BEGIN TRANSACTION -- 2nd Transaction SELECT @@TRANCOUNT AS TrxCount
Let’s insert another record and then view its contents. Mind you, we are currently in a nested transaction.
INSERT INTO PERSON VALUES ('Lisa', 'Ray', 'F')
SELECT * FROM PERSON
Now, let’s rollback the transaction and do a count immediately afterwards.
--Rollback Transaction ROLLBACK SELECT * FROM PERSON SELECT @@TRANCOUNT AS TrxCount
The rollback rollsback to the outermost transaction that was defined. Another thing to keep in mind is that you don’t need a COMMIT TRANSACTION in order to ROLLBACK.
