Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
TRIGGER_NESTLEVEL ( [ object_id ] , [ 'trigger_type' ] , [ 'trigger_event_category' ] )
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.
- 'trigger_type'
Specifies whether to apply TRIGGER_NESTLEVEL to AFTER triggers or INSTEAD OF triggers. Specify AFTER for AFTER triggers. Specify IOT for INSTEAD OF triggers. If trigger_type is specified, trigger_event_category must also be specified.
- 'trigger_event_category'
Specifies whether to apply TRIGGER_NESTLEVEL to DML or DDL triggers. Specify DML for DML triggers. Specify DDL for DDL triggers. If trigger_event_category is specified, trigger_type must also be specified. Note that only AFTER can be specified with DDL, because DDL triggers can only be AFTER triggers.
Remarks
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.
Examples
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)
C. Testing the nesting level of all triggers executed
IF ( (SELECT trigger_nestlevel() ) > 5 )
RAISERROR
('This statement nested over 5 levels of triggers.',16,-1)