Triggers and Transaction Nesting

To demonstrate the relationship between a trigger and the transaction nesting level, you can use the following SQL code to create a trigger on the employee table:


The purpose of this trigger is simply to show the state of the @@trancount within the trigger as the deletion is taking place.

If you now execute code for implied and explicit transactions, you can see the values of @@trancount and behavior of the batch. First, here’s the implied transaction:


The results of this are as follows:

Because no transaction ...

Get Microsoft® SQL Server 2008 R2 Unleashed now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.