Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
    
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 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 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 at 11:58
2  
@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 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 at 17:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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