My understanding is that the firing order is as follows:
answered Jan 12 '10 at 03:43 AM
You also asked for the reason. An INSTEAD OF trigger has to fire first since it completely replaces the operation. Then constraints are checked (since we now have all the new data in place). Finally, AFTER triggers fire. AFTER triggers are not part of the query plan for the original data modification - a new plan is created for them - so logically they must come last.
answered Jan 14 '10 at 02:32 AM