Don't let trigger take control

In the genesis of Database Systems age, as triggers were proposed¹, the goal of such DB mechanisms, was relatively clear. Triggers appeared for assuring and extending the control of integrity property in Database Systems:

… Database Trigger is predefined database procedure, conditionally or unconditionally succeeding or preceding other database operations automatically…[IBM Research Report, RJ1820, 1976]

The question

That is actually what technically DB Triggers are still today. However having procedures (aka triggers) on the database level makes them available for anyone. Having *easy place" for logic. So it’s no wondering that a lot of data-centric systems have designed parts of business logic as DB triggers. Far more beyond supporting audit or security routines, but also core logic can be in the triggers (and of course store procedures)

The question is how much logic should be put inside of Database? A what point is starts to smell?

The Answer

The academia and practitioner are clear at this today: 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 it is).

I think everybody understands why dealing with triggers is not comparable with 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 idea, that (all) logic should be done in the Database-tier. Of course Rich Feature Database Systems have better Sales points and once invested a lot of money to a system - manages ask you to use that system! But at the end of the day we should be carefully here.

Take-away tips

Concluding there are some tips when to use triggers:

1. Use them for integrity control, they where invented for it.
2. Use them careful for audit trails. Consider here, triggers are transaction safe, so when an operation is rolled back, all its triggered operations are also rolled back.
3. Use trigger careful for other administrative things like replication or notification.
4. But avoid to use triggers for any 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 very bad - 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.