O'Reilly logo

Microsoft SQL Server 2014 Unleashed by Paul Bertucci, Alex T. Silverstein, Chris Gallelli, Ray Rankins

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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:

use AdventureWorks2012goCREATE TRIGGER tD_auditlog ON employeeFOR DELETEAS   DECLARE @msg VARCHAR(255)   SELECT @msg = 'Trancount in trigger = ' + CONVERT(VARCHAR(2), @@trancount)   PRINT @msg   RETURNgo

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:

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required