Ask the Expert

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?

    Requires Free Membership to View

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 .VO7aaqqaAFk.0@/search390>discussion forums.

This was first published in October 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: