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