Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Returns the number of triggers executed for the statement that fired the trigger. TRIGGER_NESTLEVEL is used in DML and DDL triggers to determine the current level of nesting.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
When no parameters are specified, TRIGGER_NESTLEVEL returns the total number of triggers on the call stack. This includes itself. Omission of parameters can occur when a trigger executes commands causing another trigger to be fired or creates a succession of firing triggers.
To return the total number of triggers on the call stack for a particular trigger type and event category, specify object_id = 0.
TRIGGER_NESTLEVEL returns 0 if it is executed outside a trigger and any parameters are not NULL.
When any parameters are explicitly specified as NULL, a value of NULL is returned regardless of whether TRIGGER_NESTLEVEL was used within or external to a trigger.
A. Testing the nesting level of a specific DML trigger
IF ( (SELECT TRIGGER_NESTLEVEL( OBJECT_ID('xyz') , 'AFTER' , 'DML' ) ) > 5 ) RAISERROR('Trigger xyz nested more than 5 levels.',16,-1)
B. Testing the nesting level of a specific DDL trigger
IF ( ( SELECT TRIGGER_NESTLEVEL ( ( SELECT object_id FROM sys.triggers WHERE name = 'abc' ), 'AFTER' , 'DDL' ) ) > 5 ) RAISERROR ('Trigger abc nested more than 5 levels.',16,-1)