sql constraint Vs triggers

Question

question

Nani asked Jan 12, '10

sql constraint Vs triggers

For a particular column if both trigger and constraint are assigned. which one is going fire first and why?

t-sql trigger constraint

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-01-12T03:43:21Z

Steinar answered Jan 12, '10

My understanding is that the firing order is as follows:

Instead of trigger
Constraints
After trigger

http://msdn.microsoft.com/en-us/library/aa224775(SQL.80).aspx

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-01-14T02:32:50Z

SQL Kiwi answered Jan 14, '10

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question