SQL Server触发器 - 执行顺序

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.

谢谢。

10 个解决方案

#1

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.

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

#2

You can use sp_settriggerorder to 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 particularly so 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.

但是，我认为你有一个单一的触发器做多件事会好得多。如果订单很重要，则尤其如此，因为如果您有多个触发器，那么重要性将不会非常明显。想象一下有人试图在数月/数年内支持数据库。当然有可能存在需要多个触发器的情况，或者它确实是更好的设计，但我开始假设你应该有一个并从那里开始工作。

#3

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, but the 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.

触发器总是看起来像一个真正的整洁解决方案，并在正确的地方，它们是非常有价值的，但价格高，很容易与他们创建调试噩梦。过去我试图调试一些不起眼的数据库行为只是为了找到原因被忽略的触发器中的一部分而丢失了很多个小时。

#4

sp_settriggerorder only applies to AFTER triggers.

sp_settriggerorder仅适用于AFTER触发器。

#5

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 } ]

#6

Use sp_Settriggerorder stored 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”可以取三个值，这意味着它可以考虑最多三个触发器。

First – Trigger is fired first
首先 - 首先触发触发器
Last - Trigger is fired last
最后 - 触发器最后触发
None – Trigger is fired in random order.
无 - 触发器以随机顺序触发。

#7

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在这方面的行为不同。

#8

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.

第一：首先触发触发器。

Last : Trigger is fired last.

最后：触发器最后被触发。

None : Trigger is fired in undefined order.

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

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

并查看此链接以获取@stmttype：DDL Events的值

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

对于@namespace = {'DATABASE'| '服务器'| NULL}有关更多信息，请参阅：DDL触发器

#9

Use this system stored procedure:

使用此系统存储过程：

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

#10

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

#1