We have 2 SQL Server 2008 environments with some triggers against a table containing business logic

Within the 2 environments, we can see that the triggers are firing in a different order (thanks to print statements in the triggers)

How can I VIEW the trigger firing order within each environment? I know I can use sp_settriggerorder to set them but we want to see what the differences are in the two environments


sp_settriggerorder only allows you to set first/last, nothing in between. How SQL Server will fire the middle ones (or all, if you haven't used that) is completely up to SQL Server, it may not even be deterministic, and how it will fire them the next time the table is touched is not stored anywhere. Even the documentation states that the order is "undefined"...

If the order of the trigger firing matters then I strongly recommend consolidating them and not having a bunch of separate triggers in the first place. Since the behavior is undefined, even if you observed one day that triggers were firing a,b,c - nothing would prevent SQL Server from firing them c,a,b the next day. If you have used sp_settriggerorder to set first/last, you can see those manually using OBJECTPROPERTY as demonstrated in this answer. But if you have more than three, there is no way to predict the order of the ones you haven't marked first or last.

  • IMHO putting BL in triggers is a bad practice. If you need to get this train anyway and get dificult to consolidate the triggers you can try change then to SPs and make the trigger call the first and the first call the second – jean Sep 2 '14 at 11:38
  • 1
    @jean I don't know that I agree with that; like cursors and dynamic SQL, everything has a time, place and appropriate purpose. – Aaron Bertrand Sep 2 '14 at 11:50
  • I agree with you about using the right tool for the right job but experience showed me BL should be left at the BL layer. As an example we let BL inside the DB for performance purposes or the simplicity of using triggers to log operations but if you can move BL out of the BD you should do it – jean Sep 2 '14 at 11:58
  • 3
    @jean "business logic" is a vague and overloaded term. Neither of us has any idea what the OP really even means by that. Let's leave that problem to a different question - like one about specific business logic and whether in that specific case it should be in the database or not. It's certainly not something I can condone painting with such a broad brush. This question was not about the business logic at all. – Aaron Bertrand Sep 2 '14 at 12:20
  • Thanks all for your comments. It is a legacty application that I am bug fixing. Really appreciate the inout - I'm certainly going to consolidate it into one trigger otherwise we are at the mercy of SQLServer's whims! – Mike Sep 2 '14 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.