Logo: TUG TORONTO USERS GROUP for Midrange Systems
e -server magazine

September 1996: Volume 12, Number 1

Trigger Happy on the AS/400!

By Thibault Dambrine

oing the same thing over and over again tends to breed boredom and mistakes. This is true for every profession. For our benefit, the AS/400 operating system is taking one more step to make our programming life more fruitful and possibly less repetitive. Triggers is the subject of this article. This is a new feature on the AS/400. It is a simple concept and it can be applied in a lot of situations where data checks and edits are repeated from one program to another. As time goes by, databases grow. The working life of the average programmer is divided into two halves:

What are DB2/400 Triggers and What Do They Do?

A trigger is an action, or set of actions that are invoked automatically when a specified operation is performed on a specified physical file. The operation can be an insert, an update, or a delete instruction in a high level language statement in an application program. The action is executed by a user-written program. Database users can use triggers to: validate input data; query from other files for cross referencing purposes; enforce business rules; access system functions (for example, print an exception message when a rule is violated); replicate data to different files to achieve data consistency, and write to other files for audit trail purposes.

Benefits of Triggers

The following benefits can be realized in customers' business environments:

How to Define a Trigger

There are three basic components of a DB2/400 Trigger. The first is a physical file to which the trigger will be linked. The second is a trigger event definition, which is the action that will set off or "pull" the trigger. This can be an add, update or delete record within the chosen physical file. Finally there must be a trigger time definition, the time at which the trigger will be "pulled". This can be before the event, after the event, or both. You can associate a maximum of six triggers to one physical file, one for each of the following situations: before an insert; after an insert; before a delete; after a delete; before an update; and, after an update.

A trigger program will be literally called as a result of the trigger being "pulled". It will be in a position to receive information about the record to be added or updated in the physical file to monitor and it will take action based on that data. This can mean to perform a number of other database actions (such as other writes) or a check from one file to an other which can result in success or failure. The failure will be detected by the operating system. The trigger program is user-written. It can be written in any high-level language currently available on the AS/400.

Adding, Removing and Displaying a Trigger

A trigger is added to a physical file using the Add Physical File Trigger (ADDPFTRG) command. Once the association exists between a file and a trigger program, the system calls the trigger program when a data operation is initiated against the physical file, any member of the physical file, and any logical file created over the physical file.

The Remove Physical File Trigger (RMVPFTRG) command removes the association of file with its trigger program. Once you remove this association, no action is taken if a change is made to the physical file. The trigger program, however, remains on the system.

To display the components of a trigger, simply use the Display File Description (DSPFD) command. It provides a list of the triggers associated with a file. Specify TYPE(*TRG) or TYPE(*ALL) to get this list. The information provided is:

The firing of one trigger program can cause another trigger which can cause an avalanche of nested triggers, recursive triggers and potentially looping triggers. Infinite loops are possible in this scenario. Careful planning and testing should help avoid this type of accident. DB2/400 currently allows a maximum of 200 levels of nested triggers.

Application Performance Considerations

If you take the same edit from a number of application programs and put it instead in a trigger program, the performance impact should be minimal, since the same type of processing is done, only at a different level. Adding cascading or nested triggers on a database file where there was none before can translate into a noticeable downgrade in performance. The following example, from the DB2/400 Programmer's guide, illustrates the potential use for triggers:

Using Triggers in Conjunction with Referential Constraints

Example of Trigger Application What are the basics of referential integrity? Referential integrity is a broad term encompassing all the mechanisms and techniques that ensure the validity of referenced data in a relational database. Referential constraints are a mechanism to enforce a level of validity desired in your database. Beginning with Version 3 Release 1 this capability became part of the OS/400 Operating System.

Database users want referential integrity implemented in their database management system for several reasons:

When used in conjunction with referential integrity, triggers on a given file are processed first, along with all nested triggers. Once that action is complete, the referential integrity check occurs and verifies that the changes conform to the rules established. The program can thus resolve integrity violations prior to the referential constraint check. This is a powerful feature! Note that when using a trigger program in conjunction with referential integrity, you must journal the files affected. Note also that a trigger program can verify the data being written and send a signal based on what it found. It cannot however modify the data you are about to write to or update in a database file.

Commitment Control Considerations

A trigger program can perform a number of updates, as we have seen in the example shown earlier. It can also do more elaborate checks in conjunction with these updates. These checks can result in success or failure. For example: Assume you have three nested triggers on three different files; A, B and C. Your high level language application writes a record to file A. This triggers a (nested trigger) program to write to file B. This in turn triggers a (nested trigger) program that writes to file C and does a reference check to ensure that all three files are as expected. Now, let the reference check made by the trigger attached to file B fail and send back a negative result for the final check to OS/400. In a situation where files A, B and C would be journaled, all the trigger-written records would be automatically rolled back. If these files were not journaled, then the data would remain updated as is after the trigger was processed, good or bad.

This is a very basic example to illustrate the potential for combining triggers with commitment control. There are other ramifications beyond this simple example in such a situation and serious reading of reference manuals is recommended before starting on this path. Note that triggers can be used without journaling. A trigger program can be used, for example, even to send a message to a workstation. Basically, it will do anything you can now do in a high level language program.

In Conclusion

Triggers are still new to most of us. In this article, we only have skimmed the surface. The concept is simple enough: Attach the process to the database level, and forget having to put it at the application level. Great! But then there are so many other considerations. Commitment control can be a key component of a trigger-driven strategy for database integrity. It must however, be well understood and planned before starting out. Referential constraints is an other powerful new tool available and it seems like a natural to work hand-in-hand with triggers. These two examples are obvious ones, but truly, the potential for using trigger process in daily data processing situations can be much wider.

There is so much to say and we have not even touched the subject of writing an actual trigger program! This article is really only an introduction to DB2/400 Triggers; an invitation to further reading on the subject. Following this paragraph is a bibliography that you will likely find useful to dig into before starting to write your first trigger-driven applications.


DB2/400 Design Concepts:
Referential Integrity and Triggers,
Sharon L. Hoffman, Midrange Computing
ISBN 1-883884-29-2

DB2/400 The New AS/400 Database,
Skip Marchesani, Midrange Computing
ISBN 1-883884-12-8

DB2/400 Advanced Database Functions,
IBM Redbook: GG24-42-49-00

AS/400 DB2/400 Database
Programming Version 3,
IBM Manual: SC41-3701-00

T < G

Thibault Dambrine, a regular contributor to the TUG Magazine, works as an independent contractor in Calgary, Alberta.