Returns the number of triggers executed for the UPDATE, INSERT, or DELETE statement that fired the trigger. TRIGGER_NESTLEVEL is used in triggers to determine the current level of nesting.
TRIGGER_NESTLEVEL ( [ object_id ] )
Is the object ID of a trigger. If object_id is specified, the number of times the specified trigger has been executed for the statement is returned. If object_id is not specified, the number of times all triggers have been executed for the statement is returned.
When object_id is omitted (this is different from a null value), TRIGGER_NESTLEVEL returns the number of triggers on the call stack, including itself. Omission of object_id can occur when a trigger executes commands causing another trigger to be fired or creates a succession of firing triggers.
TRIGGER_NESTLEVEL returns 0 if it is executed outside of a trigger and object_id is not NULL.
TRIGGER_NESTLEVEL optionally receives an object ID as its argument. When object_id is explicitly specified as NULL or an invalid object id is referenced, a value of NULL is returned regardless of whether TRIGGER_NESTLEVEL was used within or external to a trigger.
A. Test nesting level of a specific trigger
IF ( (SELECT trigger_nestlevel( object_ID('xyz') ) ) > 5 ) RAISERROR('Trigger xyz nested more than 5 levels.',16,-1)
B. Test nesting level of all triggers executed
IF ( (SELECT trigger_nestlevel() ) > 5 ) RAISERROR ('This statement nested over 5 levels of triggers.',16,-1)