Applies To: SQL Server 2014, SQL Server 2016 Preview
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.
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.
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.
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.
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.