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 2014 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.
