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:
Get Microsoft® SQL Server 2012 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
