Don’t let trigger take control
In earlier times of Database Systems, as triggers were proposed (e.g. by Eswaran1, Eswaran2 ), 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 also for modelling (parts) of application logic or business rules. The main question, that appears is what should triggers do, what it can be used for, and when it becomes ugly? Generalising question thereby is, how much business logic should be done by the Database systems.
You can quick “google” for some answers like evans or from codeproject. But also academia gives similar answers. And this is good so, then there is no need to put application logic to Database tier, contrariwise logic in the database tier is contra productive.
At the moment i have to deal with some very large logistic system which logic is mostly implemented as triggers, not even stored procedures but triggers. The decision to build that system on triggers is unforgivable, even if that System was concept for years (i don’t know how old is it). I thing everybody understand why dealing with triggers is not comparable with one clean implementation of logic through e.g. 2EE 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:
1. Use them for Integrity control! There where invented for it!
2. 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.
3. Use trigger careful for other administrative things like replication or notification.
4. Avoid to use triggers for business logic!
P.S. Next time i maybe explain why it is bad idea to programm Business applications in Visual Basic or atomate business processes witch MS Access )))
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Comments
No comments yet.
Leave a comment