适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

返回为激发触发器的语句执行的触发器数。Returns the number of triggers executed for the statement that fired the trigger. TRIGGER_NESTLEVEL 在 DML 和 DDL 触发器中用以确定当前的嵌套级别。TRIGGER_NESTLEVEL is used in DML and DDL triggers to determine the current level of nesting.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions


TRIGGER_NESTLEVEL ( [ object_id ] , [ 'trigger_type' ] , [ 'trigger_event_category' ] )  


触发器的对象 ID。Is the object ID of a trigger. 如果指定了 object_id,则返回为该语句执行指定触发器的次数。If object_id is specified, the number of times the specified trigger has been executed for the statement is returned. 如果未指定 object_id,则返回为该语句执行全部触发器的次数。If object_id is not specified, the number of times all triggers have been executed for the statement is returned.

' trigger_type '' trigger_type '
指定将 TRIGGER_NESTLEVEL 应用于 AFTER 触发器还是 INSTEAD OF 触发器。Specifies whether to apply TRIGGER_NESTLEVEL to AFTER triggers or INSTEAD OF triggers. 为 AFTER 触发器指定 AFTER。Specify AFTER for AFTER triggers. 为 INSTEAD OF 触发器指定 IOT。Specify IOT for INSTEAD OF triggers. 如果指定了 trigger_type,则必须指定 trigger_event_category。If trigger_type is specified, trigger_event_category must also be specified.

' trigger_event_category '' trigger_event_category '
指定将 TRIGGER_NESTLEVEL 应用于 DML 触发器还是 DDL 触发器。Specifies whether to apply TRIGGER_NESTLEVEL to DML or DDL triggers. 为 DML 触发器指定 DML。Specify DML for DML triggers. 为 DDL 触发器指定 DDL。Specify DDL for DDL triggers. 如果指定了 trigger_event_category,则必须指定 trigger_type。If trigger_event_category is specified, trigger_type must also be specified. 注意,由于 DDL 触发器只能是 AFTER 触发器,因此仅 AFTER 可以使用 DDL 指定。Note that only AFTER can be specified with DDL, because DDL triggers can only be AFTER triggers.


如果未指定参数,则 TRIGGER_NESTLEVEL 返回调用堆栈上的触发器总数。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.

若要针对特殊触发器类型和事件类别返回调用堆栈上的触发器总数,请指定 object_id = 0。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 在触发器的外部执行,且任何参数均不为 NULL,则 TRIGGER_NESTLEVEL 返回 0。TRIGGER_NESTLEVEL returns 0 if it is executed outside a trigger and any parameters are not NULL.

如果将任何参数显式指定为 NULL,则无论在触发器内部还是外部使用 TRIGGER_NESTLEVEL,都将返回值 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.A. 测试特定 DML 触发器的嵌套级别Testing the nesting level of a specific DML trigger

   RAISERROR('Trigger xyz nested more than 5 levels.',16,-1)  

B.B. 测试特定 DDL 触发器的嵌套级别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.C. 测试执行的所有触发器的嵌套级别Testing the nesting level of all triggers executed

IF ( (SELECT trigger_nestlevel() ) > 5 )  
      ('This statement nested over 5 levels of triggers.',16,-1)  

另请参阅See Also