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
Best Answer
sp_settriggerorderonly 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_settriggerorderto set first/last, you can see those manually usingOBJECTPROPERTYas 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.