Three rules of thumb for triggers
We have had a lot of discussions about triggers. Some say it's better to write the code in an application program and some say it's better with a trigger.
If we should use either one, when should we use it? Some say -- don't use AFTER-triggers or not on high transaction applications. Is that true?
The general rules of thumb I advocate for triggers are as follows:
1. Favor triggers when data modifications can be done outside the control of planned applications. Doing so will enforce your integrity constraints in the triggers that would not be enforced otherwise.
2. Triggers are less efficient than constraints. If you can accomplish the same effect with referential, unique or check constraints do so, as it will be more efficient.
3. If all modifications to the data are done through transactions, performing the code in the application logic instead of a trigger will likely be more efficient.
As with most things, your exact mileage will vary. In other words, you should investigate using triggers where it makes sense given the business rules that apply to your data. For applications with very high transaction rates be sure to thoroughly test the impact of triggers on your system before committing to them.
Triggers provide many potential benefits for enhancing DB2 databases. With triggers your DB2 tables can become active -- meaning that DB2 will take action implicitly as the data in the database is modified. Triggers are a very powerful way to enhance the data integrity of your DB2 databases.
Still, keep these additional cautions in mind:
- When triggers are added to a table, they are not effective on existing data.
- Triggers are not fired for the LOAD Utility (except for the LOAD SHRLEVEL CHANGE).
Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our
This was first published in October 2004