skreebydba

Hi Frank,

Thank you for your enjoyable and informative presentation. I think I found the answer regarding nested transactions. The following topics are taken from Books Online.

Nesting Transactions
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/8cca105f-6d39-491f-9a85-e11cd4220cc1.htm
Committing 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.
If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

Using DML Triggers That Include COMMIT or ROLLBACK TRANSACTION
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/d7d7fd3f-50b7-4f8e-b03f-195844aee441.htm
If triggers that include ROLLBACK TRANSACTION statements are fired from within a user-defined transaction, ROLLBACK TRANSACTION rolls back the whole transaction.
When triggers that include COMMIT or ROLLBACK TRANSACTION statements are executed from a batch, they cancel the whole batch.
We do not recommend placing COMMIT TRANSACTION or COMMIT WORK statements in a trigger.

Rollbacks and Commits in Stored Procedures and Triggers
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/650105c1-32fd-4f16-8082-f391c42c3fb0.htm
If @@TRANCOUNT has a different value when a stored procedure finishes than it had when the procedure was executed, an informational error (266) occurs.
(RA: That is if there was a ROLLBACK, or COMMIT without a BEGIN TRANSACTION in the stored procedure.)
If the trigger completes execution and @@TRANCOUNT = 0, error 3609 occurs and the batch is terminated. If a BEGIN TRANSACTION statement is issued in a trigger, it creates a nested transaction. In this situation, when a COMMIT TRANSACTION statement is executed, the statement will apply only to the nested transaction.

Intentionally nesting transactions doesn’t buy you anything. This capability is available to allow stored procedures, triggers, or batches (and probably functions) containing a transaction to call other stored procedures or triggers containing a transaction. It should not be considered as a useful programming construct to allow you to rollback sections inside a transaction. For that savepoints are available.

I think this answers our question. Thanks again for your well prepared and informative talk.

Robert