In earlier times of Database Systems, as triggers were proposed (e.g. by Eswaran: "Functional Specifications of Subsystem for Database Integrity"), the goal of such DB mechanisms like trigger, was clear.
Triggers appears for extending of Integrity control> in Database Systems and became following definition:
... Database Trigger is predefined database procedure, conditionally or unconditionally succeeding or preceding other database operations automatically...[IBM Research Report, RJ1820, 1976]
That is actually what triggers are still today. But alone that definition make clear, that triggers can be also exploited for other purposes, like audit or security, but unfortunately also for modelling (parts) of application logic or business rules.
The question is how much logic should be put inside of Database? A what point is starts to smell?
The academia and practitioner are clear at this:
There is no need to put application logic into to persistence tier, contrariwise logic in the database tier is contra-productive.
At the moment i have to deal with some very large IT system in the domain of logistics. The business logic is mostly implemented as database triggers, not even stored procedures but triggers. The decision to build that system on triggers is unforgivable, even if that System was designed years ago, maybe more than a one decade (i don't know how old is it).
I think everybody understand why dealing with triggers is not comparable with one clean implementation of logic through e.g. tag based Middle-tier. At first the main logic and rules of the system are spread in a hundreds of triggers over hundreds of tables and therefore it is very difficult to maintenance. Secondly there are many side effects present, and many of them are unfortunately hard to predict, so that some kind of not determinism is always in the air. And at third there are problems on bulk updates, of course, when triggers start the avalanche, cascading each other!
Well, the richness of DB functions and options alone, should not lead to the meaning that all logic should be done in the Database-tier. Of course Database Systems which are rich on functions sells better. Yea! Maybe even better than sex :). But at the End we should be carefully with every extra!
Concluding there are some tips when to use triggers:
- Use them for Integrity control! There where invented for it!
- Use them careful for Audit trails, consider also triggers are transaction safe, so when an operation is rolled back, all its triggered operations are also rolled back.
- Use trigger careful for other administrative things like replication or notification.
- But avoid to use triggers for business logic!
Update Dez 2014: Looking back to this 7 years old post i see how illusory i was about real IT in (other) companies. Now i gained some more background in consulting and i have seen a lot of creepy, grown over time, strange IT solutions. Now, the idea of putting some business logic to database tier seem not to be the worst thing i saw, but still worst, worst -don't do it!
I also understand much more, how bad organizations influence bad architectures... Meanwhile i can say i've saw how IT Architecture follows IT Organization like Conway says.