指定第一个和最后一个触发器Specify First and Last Triggers

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

可将与表关联的 AFTER 触发器之一指定为执行每个 INSERT、DELETE 和 UPDATE 触发操作时激发的第一个或最后一个 AFTER 触发器。You can specify that one of the AFTER triggers associated with a table be either the first AFTER trigger or the last AFTER trigger that is fired for each INSERT, DELETE, and UPDATE triggering actions. 在第一个和最后一个触发器之间激发的 AFTER 触发器将按未定义的顺序执行。The AFTER triggers that are fired between the first and last triggers are executed in undefined order.

若要指定 AFTER 触发器的顺序,请使用 sp_settriggerorder 存储过程。To specify the order for an AFTER trigger, use the sp_settriggerorder stored procedure. sp_settriggerorder 有下列选项。sp_settriggerorder has the following options.

选项Option 描述Description
第一个First 指定 DML 触发器是执行触发操作时激发的第一个 AFTER 触发器。Specifies that the DML trigger is the first AFTER trigger fired for a triggering action.
上一次Last 指定 DML 触发器是执行触发操作时激发的最后一个 AFTER 触发器。Specifies that the DML trigger is the last AFTER trigger fired for a triggering action.
None 指定不按特定的顺序激发 DML 触发器。Specifies that there is no specific order in which the DML trigger should be fired. 主要用于将某个触发器重置为第一个或最后一个触发器。Used mainly to reset a trigger from being either first or last.

以下示例说明如何使用 sp_settriggerorderThe following example shows using sp_settriggerorder:

sp_settriggerorder @triggername = 'MyTrigger', @order = 'first', @stmttype = 'UPDATE'  

重要

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

可以同时为表定义 INSERT、UPDATE 和 DELETE 触发器。A table can have INSERT, UPDATE, and DELETE triggers defined on it at the same time. 每个语句类型都可以有其自己的第一个和最后一个触发器,但它们不能是相同的触发器。Each statement type can have its own first and last triggers, but they cannot be the same triggers.

如果为某个表定义的第一个或最后一个触发器不包括触发操作,如 FOR UPDATE、FOR DELETE 或 FOR INSERT,则缺少的操作将没有第一个或最后一个触发器。If the first or last trigger defined for a table does not cover a triggering action, such as not covering FOR UPDATE, FOR DELETE, or FOR INSERT, there is no first or last trigger for the missing actions.

不能将 INSTEAD OF 触发器指定为第一个或最后一个触发器。INSTEAD OF triggers cannot be specified as first or last triggers. 在对基础表进行更新前激发 INSTEAD OF 触发器。INSTEAD OF triggers are fired before updates are made to the underlying tables. 如果由 INSTEAD OF 触发器对基础表进行更新,这些更新将在激发为表定义的任何 AFTER 触发器之前发生。If updates are made by an INSTEAD OF trigger to underlying tables, the updates occur before any AFTER triggers defined on the table are fired. 例如,如果视图上的一个 INSTEAD OF INSERT 触发器将数据插入某个基表,而基表本身包含一个 INSTEAD OF INSERT 触发器和三个 AFTER INSERT 触发器,则会激发基表上的 INSTEAD OF INSERT 触发器而不发生插入操作,而基表上 AFTER 触发器将在执行所有插入操作后激发。For example, if an INSTEAD OF INSERT trigger on a view inserts data into a base table and the base table itself contains an INSTEAD OF INSERT trigger and three AFTER INSERT triggers, the INSTEAD OF INSERT trigger on the base table is fired instead of the inserting action, and the AFTER triggers on the base table are fired after any inserting action on the base table. 有关详细信息,请参阅 DML TriggersFor more information, see DML Triggers.

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

OBJECTPROPERTY 函数使用以下属性来报告某个触发器是第一个还是最后一个触发器:ExecIsFirstInsertTrigger、ExecIsFirstUpdateTrigger、ExecIsFirstDeleteTrigger、ExecIsLastInsertTrigger、ExecIsLastUpdateTrigger 和 ExecIsLastDeleteTrigger 。The OBJECTPROPERTY function reports whether a trigger is a first or last trigger by using the followwing properties: ExecIsFirstInsertTrigger, ExecIsFirstUpdateTrigger, ExecIsFirstDeleteTrigger, ExecIsLastInsertTrigger, ExecIsLastUpdateTrigger, and ExecIsLastDeleteTrigger.

复制将为包含在立即更新订阅或排队更新订阅中的任意表自动生成第一个触发器。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,或使用 sp_settriggerorder 将复制触发器更改为最后一个触发器或无触发器,订阅将无法正常工作。If you use ALTER on the replication trigger or use sp_settriggerorder to change the replication trigger to a last or none trigger, the subscription will not function correctly.

另请参阅See Also

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