SQL Server 触发器 - 执行顺序

SQL Server triggers - order of execution

提问by HAdes

Does anyone know how SQL Server determines the order triggers (of same type, i.e. before triggers) are executed. And is there any way of changing this so that I can specify the order I want. If not, why not.

有谁知道 SQL Server 如何确定执行触发器（相同类型，即触发器之前）的顺序。有什么方法可以改变它，以便我可以指定我想要的顺序。如果没有，为什么不呢。

Thanks.

谢谢。

回答by JosephStyons

Using SetTriggerOrder is fine, but if your code depends on a specific sequence of execution, why not wrap all your triggers into stored procedures, and have the first one call the second, the second call the third, etc.

使用 SetTriggerOrder 很好，但是如果您的代码取决于特定的执行顺序，为什么不将所有触发器包装到存储过程中，并让第一个调用第二个，第二个调用第三个等等。

Then you simply have the first one execute in the trigger.

然后您只需在触发器中执行第一个。

Someone in the future will be grateful that they didn't have to dig around in a system table to determine a custom execution sequence.

将来有人会很感激他们不必在系统表中挖掘来确定自定义执行顺序。

回答by Rory

You can use sp_settriggerorderto define the order of each trigger on a table.

您可以使用sp_settriggerorder定义表上每个触发器的顺序。

However, I would argue that you'd be much better off having a single trigger that does multiple things. This is particularlyso if the order is important, since that importance will not be very obvious if you have multiple triggers. Imagine someone trying to support the database months/years down the track. Of course there are likely to be cases where you need to have multiple triggers or it really is better design, but I'd start assuming you should have one and work from there.

但是，我认为您最好拥有一个可以执行多项操作的触发器。如果顺序很重要，则尤其如此，因为如果您有多个触发器，那么重要性不会很明显。想象一下有人试图支持数据库几个月/几年的轨道。当然，在某些情况下，您可能需要多个触发器，或者它确实是更好的设计，但我开始假设您应该拥有一个并从那里开始工作。

回答by Cruachan

If your at the point of worrying about trigger orders then you really should take a step backwards and consider what you are trying to do and if there is there a better way of doing it. The fact that this isn't an easy thing to change should be telling you something.

如果您担心触发订单，那么您真的应该退后一步，考虑一下您要做什么以及是否有更好的方法来做。这不是一件容易改变的事情的事实应该告诉你一些事情。

Triggers always look like a real neat solution, and in the right place they are highly valuable, butthe price is high, it's really easy to create debugging nightmares with them. I've lost many hours in the past trying to debug some obscure database behavior only to find the cause is burrowed away in an overlooked trigger.

触发器总是看起来像一个真正巧妙的解决方案，在正确的地方它们非常有价值，但价格很高，很容易用它们制造调试噩梦。过去我花了很多时间试图调试一些晦涩的数据库行为，结果却发现原因隐藏在一个被忽视的触发器中。

回答by Luke Bennett

sp_settriggerorder only applies to AFTER triggers.

sp_settriggerorder 仅适用于 AFTER 触发器。

回答by Frosty

You can guarantee which trigger is fired first, which trigger is fired last and which ones fire in the middle by using sp_settriggerorder. If you need to synchronize more than three it does not appear possible in SQL Server 2005.

您可以使用 sp_settriggerorder 保证首先触发哪个触发器，最后触发哪个触发器，以及在中间触发哪个触发器。如果您需要同步三个以上，则在 SQL Server 2005 中似乎不可能。

Here is a sample taken from here(The linked article has much more information).

这是取自此处的示例（链接的文章有更多信息）。

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

回答by EvilEddie

Use sp_Settriggerorderstored procedure, you can define the execution order of the trigger.

使用sp_Settriggerorder存储过程，可以定义触发器的执行顺序。

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

The second parameter, “order” can take three values which means that it can take into account up-to three triggers.

第二个参数“order”可以取三个值，这意味着它最多可以考虑三个触发器。

1. First – Trigger is fired first
2. Last - Trigger is fired last
3. None – Trigger is fired in random order.

1. First – 首先触发触发器
2. Last - 最后触发触发器
3. 无 - 触发器以随机顺序触发。

回答by Dave_H

The order is set by sql server, the only thing you can do is use a system sp (sp_settriggerorder) to set which trigger will fire first and which will fire last.

顺序由 sql server 设置，您唯一可以做的就是使用系统 sp (sp_settriggerorder) 来设置哪个触发器将首先触发，哪个将最后触发。

Beyond setting the first and last triggers to fire, you can't modify or tell which order sql server will use. Therefore you will want to build your triggers so they do not rely on which order they are fired. Even if you determine the order they fire in today, it may change tomorrow.

除了将第一个和最后一个触发器设置为触发之外，您无法修改或告诉 sql server 将使用哪个顺序。因此，您将希望构建触发器，以便它们不依赖于它们的触发顺序。即使您今天确定了它们的触发顺序，明天也可能会发生变化。

This information is based on Sql Server 2000, however I do not believe 2005/2008 act differently in this regard.

此信息基于 Sql Server 2000，但我认为 2005/2008 在这方面没有不同。

回答by Ardalan Shahgholi

Use This :

用这个 ：

For example :

例如 ：

USE AdventureWorks;
GO
EXEC sys.sp_settriggerorder @triggername = N'', -- nvarchar(517)
    @order = '', -- varchar(10)
    @stmttype = '', -- varchar(50)
    @namespace = '' -- varchar(10)

The First and Last triggers must be two different triggers.

First 和 Last 触发器必须是两个不同的触发器。

First : Trigger is fired first.

First ：首先触发触发器。

Last : Trigger is fired last.

Last : 最后触发触发器。

None : Trigger is fired in undefined order.

None ：触发器以未定义的顺序触发。

And see this link for value of @stmttype: DDL Events

并查看此链接以获取以下价值@stmttype：DDL 事件

And for @namespace = { 'DATABASE' | 'SERVER' | NULL } and for more information see : DDL Triggers

而对于@namespace = { 'DATABASE' | '服务器' | NULL } 和更多信息参见：DDL 触发器

回答by Ben Hoffstein

Use this system stored procedure:

使用这个系统存储过程：

sp_settriggerorder[@triggername = ] 'triggername', [@order = ] 'value', [@stmttype = ] 'statement_type'

回答by Akshay

A million dollar statement in this context -

在这种情况下，一百万美元的声明——

sp_settriggerorder: Specifies the AFTER triggers that are fired first or last. The AFTER triggers that are fired between the first and last triggers are executed in undefined order.

sp_settriggerorder：指定首先或最后触发的 AFTER 触发器。在第一个和最后一个触发器之间触发的 AFTER 触发器以未定义的顺序执行。

Source : MSDN

来源：MSDN