sp_settriggerorder (Transact-SQL)sp_settriggerorder (Transact-SQL)

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

指定第一个激发或最后一个激发的 AFTER 触发器。Specifies the AFTER triggers that are fired first or last. 在第一个和最后一个触发器之间激发的 AFTER 触发器将按未定义的顺序执行。The AFTER triggers that are fired between the first and last triggers are executed in undefined order.

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

语法Syntax

  
sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername'   
    , [ @order = ] 'value'   
    , [ @stmttype = ] 'statement_type'   
    [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]  

参数Arguments

[ @triggername = ] '[ _triggerschema.] _triggername'触发器的名称及其所属的架构 (如果适用), 其顺序为 "已设置" 或 "已更改"。[ @triggername = ] '[ _triggerschema.] _triggername' Is the name of the trigger and the schema to which it belongs, if applicable, whose order is to be set or changed. [triggerschema . ]triggernamesysname[triggerschema.]triggername is sysname. 如果名称与触发器不对应,或者名称与 INSTEAD OF 触发器对应,则该过程将返回错误。If the name does not correspond to a trigger or if the name corresponds to an INSTEAD OF trigger, the procedure returns an error. 不能为 DDL 或登录触发器指定triggerschematriggerschema cannot be specified for DDL or logon triggers.

[ @order = ] 'value'触发器的新顺序的设置。[ @order = ] 'value' Is the setting for the new order of the trigger. varchar (10) , 可以是下列值之一。value is varchar(10) and it can be any one of the following values.

重要

第一个最后一个触发器必须是两个不同的触发器。The First and Last triggers must be two different triggers.

ReplTest1Value 描述Description
第一个First 触发器被第一个触发。Trigger is fired first.
上一次Last 触发器被最后一个触发。Trigger is fired last.
None 触发器以未定义的顺序触发。Trigger is fired in undefined order.

[ @stmttype = ] 'statement_type'指定触发触发器的 SQL 语句。[ @stmttype = ] 'statement_type' Specifies the SQL statement that fires the trigger. statement_typevarchar (50) , 可以是 INSERT、UPDATE、DELETE、LOGON 或Transact-SQLTransact-SQL DDL 事件中列出的任何语句事件。statement_type is varchar(50) and can be INSERT, UPDATE, DELETE, LOGON, or any Transact-SQLTransact-SQL statement event listed in DDL Events. 不能指定事件组。Event groups cannot be specified.

只有在将触发器定义为该语句类型的触发器之后, 才能将该触发器指定为语句类型的第一个最后一个触发器。A trigger can be designated as the First or Last trigger for a statement type only after that trigger has been defined as a trigger for that statement type. 例如, 如果TR1定义为 insert 触发器, 则可以将 trigger TR1指定为 table T1上的 insert。For example, trigger TR1 can be designated First for INSERT on table T1 if TR1 is defined as an INSERT trigger. 如果仅将TR1定义为 INSERT 触发器, 则会将设置为 UPDATE 语句的第一个或最后一个触发器。 数据库引擎Database EngineThe 数据库引擎Database Engine returns an error if TR1, which has been defined only as an INSERT trigger, is set as a First, or Last, trigger for an UPDATE statement. 有关详细信息,请参阅“备注”部分。For more information, see the Remarks section.

namespace = { ' 数据库 ' | "SERVER" | @ 无效@namespace= { 'DATABASE' | 'SERVER' | NULL }
triggername是 DDL 触发器时, @命名空间指定是使用数据库作用域还是服务器作用域创建triggernameWhen triggername is a DDL trigger, @namespace specifies whether triggername was created with database scope or server scope. 如果triggername是 logon 触发器, 则必须指定服务器。If triggername is a logon trigger, SERVER must be specified. 有关 DDL 触发器作用域的详细信息, 请参阅Ddl 触发器For more information about DDL trigger scope, see DDL Triggers. 如果未指定或指定 NULL, 则triggername是 DML 触发器。If not specified, or if NULL is specified, triggername is a DML trigger.

SERVER 适用于:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017SERVER applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

返回代码值Return Code Values

0 (成功) 和 1 (失败)0 (success) and 1 (failure)

备注Remarks

DML 触发器DML Triggers

对于单个表中的每个语句, 只能有一个和最后一个触发器。There can be only one First and one Last trigger for each statement on a single table.

如果已在表、数据库或服务器上定义了第一个触发器, 则不能为同一statement_type的同一个表、数据库或服务器指定新的触发器。If a First trigger is already defined on the table, database, or server, you cannot designate a new trigger as First for the same table, database, or server for the same statement_type. 此限制也适用于最后一个触发器。This restriction also applies Last triggers.

复制将为包含在立即更新订阅或排队更新订阅中的任意表自动生成第一个触发器。Replication automatically generates a first trigger for any table that is included in an immediate updating or queued updating subscription. 复制要求其触发器为第一个触发器。Replication requires that its trigger be the first trigger. 在尝试将带有第一个触发器的表包含在立即更新订阅或排队更新订阅中时,复制将引发错误。Replication raises an error when you try to include a table with a first trigger in an immediate updating or queued updating subscription. 如果在表已经包含在订阅中之后尝试使某个触发器成为第一个触发器, sp_settriggerorder 将返回错误。If you try to make a trigger a first trigger after a table has been included in a subscription, sp_settriggerorder returns an error. 如果在复制触发器上使用 ALTER TRIGGER, 或使用sp_settriggerorder将复制触发器更改为最后一个触发器或触发器, 则订阅将无法正常运行。If you use ALTER TRIGGER on the replication trigger, or use sp_settriggerorder to change the replication trigger to a Last or None trigger, the subscription does not function correctly.

DDL 触发器DDL Triggers

如果具有数据库作用域的 DDL 触发器和具有服务器作用域的 DDL 触发器存在于同一事件上, 则可以指定两个触发器都是第一个触发器或最后一个触发器。If a DDL trigger with database scope and a DDL trigger with server scope exist on the same event, you can specify that both triggers be a First trigger or a Last trigger. 但是,服务器作用域的触发器始终最先触发。However, server-scoped triggers always fire first. 一般情况下,同一事件中 DDL 触发器的执行顺序如下:In general, the order of execution of DDL triggers that exist on the same event is as follows:

  1. 标记为First的服务器级触发器。The server-level trigger marked First.

  2. 其他服务器级触发器。Other server-level triggers.

  3. 标记为Last的服务器级触发器。The server-level trigger marked Last.

  4. 标记为First的数据库级触发器。The database-level trigger marked First.

  5. 其他数据库级触发器。Other database-level triggers.

  6. 标记为Last的数据库级触发器。The database-level trigger marked Last.

常规触发器注意事项General Trigger Considerations

如果 ALTER TRIGGER 语句更改了第一个或最后一个触发器, 则会删除最初在触发器上设置的第一个最后一个属性, 并且值将替换为 ""。If an ALTER TRIGGER statement changes a first or last trigger, the First or Last attribute originally set on the trigger is dropped, and the value is replaced by None. 必须使用sp_settriggerorder重置顺序值。The order value must be reset by using sp_settriggerorder.

如果必须将同一个触发器指定为多个语句类型的第一个或最后一个顺序, 则必须为每个语句类型执行sp_settriggerorderIf the same trigger must be designated as the first or last order for more than one statement type, sp_settriggerorder must be executed for each statement type. 此外, 必须首先为语句类型定义触发器, 然后才能将该触发器指定为要为该语句类型激发的第一个最后一个触发器。Also, the trigger must be first defined for a statement type before it can be designated as the First or Last trigger to fire for that statement type.

权限Permissions

若要设置具有服务器作用域(使用 ON ALL SERVER 创建)的 DDL 触发器或登录触发器的顺序,需要具有 CONTROL SERVER 权限。To set the order of a DDL trigger with server scope (created ON ALL SERVER) or a logon trigger requires CONTROL SERVER permission.

若要设置具有数据库作用域(使用 ON DATABASE 创建)的 DDL 触发器的顺序,需要具有 ALTER ANY DATABASE DDL TRIGGER 权限。To set the order of a DDL trigger with database scope (created ON DATABASE) requires ALTER ANY DATABASE DDL TRIGGER permission.

若要设置 DML 触发器的顺序,需要对要在其中定义该触发器的表或视图具有 ALTER 权限。To set the order of a DML trigger requires ALTER permission on the table or view on which the trigger is defined.

示例Examples

A.A. 设置 DML 触发器的触发顺序Setting the firing order for a DML trigger

以下示例指定触发器 uSalesOrderHeader 是对 UPDATE 表执行 Sales.SalesOrderHeader 操作后触发的第一个触发器。The following example specifies that trigger uSalesOrderHeader be the first trigger to fire after an UPDATE operation occurs on the Sales.SalesOrderHeader table.

USE AdventureWorks2012;  
GO  
sp_settriggerorder @triggername= 'Sales.uSalesOrderHeader', @order='First', @stmttype = 'UPDATE';  

B.B. 设置 DDL 触发器的触发顺序Setting the firing order for a DDL trigger

以下示例指定触发器 ddlDatabaseTriggerLog 是对 AdventureWorks2012AdventureWorks2012 数据库执行 ALTER_TABLE 操作后触发的第一个触发器。The following example specifies that trigger ddlDatabaseTriggerLog be the first trigger to fire after an ALTER_TABLE event occurs in the AdventureWorks2012AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
sp_settriggerorder @triggername= 'ddlDatabaseTriggerLog', @order='First', @stmttype = 'ALTER_TABLE', @namespace = 'DATABASE';  

请参阅See Also

系统存储过程 (Transact-SQL) System Stored Procedures (Transact-SQL)
数据库引擎存储过程(transact-sql) Database Engine Stored Procedures (Transact-SQL)
ALTER TRIGGER (Transact-SQL)ALTER TRIGGER (Transact-SQL)