TRIGGER_NESTLEVEL (T-SQL)

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.

Syntax

TRIGGER_NESTLEVEL( [ object_id ] )

Arguments

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.

Remarks

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.

Examples

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)