35

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.

Thanks.

Improve this question
0

10 Answers 10

Reset to default
19

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.

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Generally we don't do this because we don't have easy access to the Inserted and Deleted pseudotables in stored procedures.
@mwigdahl Instead of writing the Insert/Update/Delete Statement ad-hoc, you should implement a design pattern where you run all your Business-Logic DML inside of Sprocs. There you will have access to the values you want to Insert/Update and can easily query for the data you will be Deleting. I'm not a fan of ORM because (as a DB purist), I think all DML should be encapsulated inside of Sprocs (except for fixes). If you can't do that (maybe it's a 3rd Party DB or you're the new guy no one will listen to about investing time in rearchitecture), then welcome to trigger-H-E-double-hockysticks.
16

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

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.

Improve this answer

1 Comment

While it's a good idea to have a single trigger if order of execution matters, this is not the case if it doesn't. Ex. - I have a project with two triggers on a table. One logs changes in case someone doesn't use the provided SP to update the table. The other acts as a FK between 3 tables with a complex relationship that cannot be described via "in-built" FK-s. The SP that updates the table disables the log trigger while it works (to avoid duplicate logs), but I still want the "key trigger" to run. If order of execution doesn't matter, group triggers by task to improve "code readability".
13

If you're 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 really neat solution, and in the right place they are highly valuable, but the price is high, and 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 that the cause is burrowed away in an overlooked trigger.

Improve this answer

1 Comment

old post, new comment - I'd like to set the order so that if something goes wrong I know what triggers have run to reach the failure point. The fact that it's not easy to change tells me that Microsoft allow multiple triggers but didn't implement it terribly well. Taking a step backwards isn't always an option. Is it any better in SQL Server 2019
7

sp_settriggerorder only applies to AFTER triggers.

Improve this answer

Comments

4

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.

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 } ]
Improve this answer

Comments

4

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

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.

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

Comments

3

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.

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.

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

Improve this answer

Comments

2

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 : 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

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

Improve this answer

Comments

1

Use this system stored procedure:

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

Comments

1

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.

Source : MSDN

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.