Performance considerations for Triggers vs Constraints

Question

I'm trying to find out whether I should be using business critical logic in a trigger or constraint inside of my database.
So far I've added logic in triggers as it gives me the control over what happens next and means I can provide custom user messages instead of an error that will probably confuse the users.

Is there any noticable performance gain in using constraints over triggers and what are the best practices for determining which to use.

Answer 1

Constraints hands down!

• With constraints you specify relational principles, i.e. facts about your data. You will never need to change your constraints, unless some fact changes (i.e. new requirements).

• With triggers you specify how to handle data (in inserts, updates etc.). This is a "non-relational" way of doing things.

To explain myself better with an analogy: the proper way to write a SQL query is to specify "what you want" instead of "how to get it" – let the RDBMS figure out the best way to do it for you. The same applies here: if you use triggers you have to keep in mind various things like the order of execution, cascading, etc... Let SQL do that for you with constraints if possible.

That's not to say that triggers don't have uses. They do: sometimes you can't use a constraint to specify some fact about your data. It is extremely rare though. If it happens to you a lot, then there's probably some issue with the schema.