DB integrity: trigger vs. keys/constraints

Question

I and my friend argued with each other about the design of a database.
He argues that to ensure the integrity of a complex database is better to use triggers.

I believe that for this purpose is better to use keys (primary, unique), and constraints.
I think that the use of triggers is dangerous, because they work "behind the scene" and it's not easy to say what will happen after the execution of a command. Moreover, if trigger has a bug it can break DB's integrity.

What do you think about it?

Answer 1

You actually don't say why your friend thinks what he thinks, but, in any case constraints/keys are the standard, defined and proper way to ensure data integrity, for two reasons:

• Everybody knows them and you'll avoid violating the principle of least surprise by using them.

• They are already implemented, tested and working.

There is no actual benefit of rolling your own data integrity code. Triggers are intended for other use cases, such as (for example) keeping a log of all insertions.

Answer 2

You didn't specify what database but I'll assume an ANSI standard, relational DBMS such as Oracle or SQL Server.

I guess it depends on what you mean by integrity. If you are only trying to keep child records and parent records all together and prevent orphans then built-in RI using primary and foreign key constraints is the way to go.

If your RI is more complicated, for example if field 1 in parent record is > 100 then field 2 in child record must be < 200. Triggers must be used.

I would not use triggers to enforce simple RI, that wheel has already been invented.

Answer 3

I don't think it's clear cut one way or the other, but fwiw, I tend to use DRI constraints for anything that can be done in a DRI constraint, and save the triggers for those things that can't be done in a DRI Constraint (like preventing overlapping dateranges)

Answer 4

"Here is an AskTom discussion on the topic. There is no hard and fast rule on the issue (else there would be no debate!)..."

Yes there is. Declarative is always better than procedurally implemented. Declarative is less prone to making errors. Declarative is easier to maintain. Declarative is more self-documenting than procedurally implemented. Declarative offers the best occasion for the DBMS to optimize, and the DBMS IS a better optimizer than the programmer, most of the time.

The only advantage in procedurally implemented is that it means jobs for those who would be without one if true declarative constraints were available, not just the poor PK+FK that we get from SQL.