April 28, 2008 at 10:28 pm
Comments posted to this topic are about the item Set trigger firing order
April 28, 2008 at 10:38 pm
I might have got the right answer for the wrong reason. The Q poser states that using 'BEFORE' and 'AFTER' gives a 3-stages-of-triggers in sequence, i.e. it won't work for 4.
Now my reading of BOL under 'Using Nested Triggers' states
'Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels.'
which works for at least 32 triggers in sequence, and maybe (going out on a limb here) 96.
Am I missing something? Or the Q poser?
BTW I am not a DBA !
April 29, 2008 at 1:46 am
But nested triggers would be triggers on different tables.
So a trigger on orderdetail that updates the order header totals which has a trigger that goes and modifies some audit tables which fires a trigger to initiate a transfer to the Stock ordering system etc...
I was unaware that you could control trigger order on multiple triggers on the same table. Personally I would try to only have one trigger on any given table, is there a reason to have more? Isn't that a bit of a debugging nightmare?
Ken
--
Every days a day at school.
April 29, 2008 at 6:33 am
The question was only concerned with 3 triggers. Setting the order the way the answer states would work. As for using the solution with more than 3 triggers, keep in mind that any triggers in between the First and Last triggers are executed in undefined order.
Thanks for the question 😀
-Jeff
April 29, 2008 at 7:56 am
When I read the question the first time, I believe I read it as the author intended; however,
Jeff's response made me think about a couple of possibilities.
Possible exceptions.
If TRB or TRC is an INSTEAD OF trigger used for UPDATING underlying tables, it will not allow the desired order. Also 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.
Great question.
Q
Please take a number. Now serving emergency 1,203,894
April 29, 2008 at 8:12 am
Ken Gaul (4/29/2008)
But nested triggers would be triggers on different tables.So a trigger on orderdetail that updates the order header totals which has a trigger that goes and modifies some audit tables which fires a trigger to initiate a transfer to the Stock ordering system etc...
I was unaware that you could control trigger order on multiple triggers on the same table. Personally I would try to only have one trigger on any given table, is there a reason to have more? Isn't that a bit of a debugging nightmare?
Ken
--
Every days a day at school.
Ken,
Occasionally I am asked to implement a custom integration with an existing application. When the integration requirement indicates a trigger is needed, the choice should always be a new trigger. If the application doesn't already have a trigger on the same table, a trigger may be added on a subsequent upgrade to the application.
The result is much more maintainable. Since custom triggers don't always survive upgrades, recreating it is simple. I also would not want to be modifying another programmer's trigger, since I may not understand all of the reasons behind it's being there or all of the functionality that it implements.
In every case it is necessary to have enough understanding of the original trigger to decide if the order of firing is important or not.
April 29, 2008 at 8:55 am
Keep in mind that the question, as asked, is possible. There are details that might make it impossible, but they aren't in the scope of the question.
As far as multiple triggers on a table, yes, that might be necessary/desirable. You might want to have one trigger for inserts, one for deletes, one for updates, for example. If the actions taken are significantly different, that might be easier than trying to have one trigger that uses the inserted and deleted tables in various joins to figure out whether the action taken was an insert, and update, or a delete.
It is possible to have nested triggers on the same table. Assume you have an insert trigger on a table, which performs an update on that same table (maybe a complex column calculation), which could fire an update trigger on that table. Also, if you have triggers that look for specific columns being updated, if one of those triggers updates a column that another trigger is watching out for, it could "nest" that trigger as well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 9:30 am
By default triggers on the same table for one action (Update as this example) will be executed in order of creation.
If triggers created in this order :
TRA
TRB
TRC
Then execution sequence of these triggers will be TRA, TRB, TRC
If triggers created in this order :
TRC
TRB
TRA
Then execution sequence of these triggers will be TRC, TRB, TRA
Thanx for any reply.
April 29, 2008 at 9:50 am
a7mad.sayed (4/29/2008)
By default triggers on the same table for one action (Update as this example) will be executed in order of creation.If triggers created in this order :
TRA
TRB
TRC
Then execution sequence of these triggers will be TRA, TRB, TRC
If triggers created in this order :
TRC
TRB
TRA
Then execution sequence of these triggers will be TRC, TRB, TRA
Thanx for any reply.
This has been my experience as well. Specifying First or Last will override the order, but the remainder of the triggers of the same type will fire in the order (their IDs were) created. This makes it simple to order them, and to reorder them, and the naming of First or Last is just an added comfort zone.
As in life, nothing is permanent. If you have specified First or Last, you aren't preventing someone else from changing that with a subsequent trigger being given the same spec.
April 29, 2008 at 11:47 am
Q (4/29/2008)
When I read the question the first time, I believe I read it as the author intended; however,Jeff's response made me think about a couple of possibilities.
Possible exceptions.
If TRB or TRC is an INSTEAD OF trigger used for UPDATING underlying tables, it will not allow the desired order. Also 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.
Great question.
As far as I know - INSTEAD OF triggers will always fire first, since they happen prior to the operation. That's why they are sometimes referred to as a BEFORE trigger. You can only have one INSTEAD OF trigger per table per operation, so this procedure doesn't involve them.
To the best of my knowledge - this question can only be taken in the context of 3 AFTER triggers. Of course - that was an immediate assumption to me, but I suppose that might be a source of confusion.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 29, 2008 at 12:51 pm
Matt Miller (4/29/2008)
Q (4/29/2008)
When I read the question the first time, I believe I read it as the author intended; however,Jeff's response made me think about a couple of possibilities.
Possible exceptions.
If TRB or TRC is an INSTEAD OF trigger used for UPDATING underlying tables, it will not allow the desired order. Also 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.
Great question.
As far as I know - INSTEAD OF triggers will always fire first, since they happen prior to the operation. That's why they are sometimes referred to as a BEFORE trigger. You can only have one INSTEAD OF trigger per table per operation, so this procedure doesn't involve them.
To the best of my knowledge - this question can only be taken in the context of 3 AFTER triggers. Of course - that was an immediate assumption to me, but I suppose that might be a source of confusion.
Matt,
Thanks for your comments. I had assumed the same thing you did, but Jeff brought up a point that this works because only 3 triggers were involved. If there had been more than 3 triggers, the triggers not designated first or last may not run in the order desired. It made me think about the question a little bit. I thought about what could prevent the order from being set.
As phrased, it says 3 update triggers and not 3 after triggers. An Instead Of trigger could be update. So I was simply observing that this would stop you from ordering it in the manner desired. Instead of triggers cannot be set in this manner. I know this is nick picking on the wording.
As far as setting a first trigger, the replication process does that, so I am not sure, but I don't believe you can set an update trigger as first when the table is included in an immediate updating or queued updating subscription. I haven't tried to set a trigger on a this type of table so I can't confirm this from experience.
Q
Please take a number. Now serving emergency 1,203,894
April 29, 2008 at 6:44 pm
Thank you one and all for the comments. Since the QOD is or should be designed to make us think and learn, I believe that all the comments have done just that, and can only conclude that the question was reasonable and met the basic objective.
Again thanks to everyone for their comments.....and discussion.
April 30, 2008 at 9:56 am
I don't think the order of creation of the triggers has any effect on the triggering order, at least not for SQL server, they say it is random. I know that this scenario exists in some DBMS but not in SQL server (to my knowledge).
the answer is to use "sp_settriggerorder" , right?
April 30, 2008 at 10:07 am
schumi (4/30/2008)
I don't think the order of creation of the triggers has any effect on the triggering order, at least not for SQL server, they say it is random. I know that this scenario exists in some DBMS but not in SQL server (to my knowledge).the answer is to use "sp_settriggerorder" , right?
There is no such thing as RANDOM. BOL merely states that the order is UNDEFINED.
Note:
sp_settriggerorder
will only set First or Last.
The creation order "theory" should be easy to disprove if that is not the case.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply