Trigger Happy on the AS/400!

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:

Half Number One: We create data models to store and organize data in space-efficient and logically organized ways.
Half Number Two: We write programs to maintain this data, while following rules we created in the first half (as best we can). This is where DB2/400 Triggers fit in.

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:

Faster application development: Because triggers are stored in the database, the actions performed by triggers do not have to be coded in each HLL application.
Global enforcement of business rules: A trigger can to be defined once and then reused for any application using the database.
Maintenance savings: If a business policy changes, it is necessary to change only the trigger program linked to a database file instead of every application program that touches it.
Improved performance in client/server environment: Triggers can be used in client/server situations where one IO from the client can in fact trigger multiple actions at the server level, thus freeing the wire between the Client and the Server for other transactions.
Automated enforcement of complex business rules: Triggers can stand alone, or can also be used in conjunction with commitment control and with referential integrity. With these tools, one could potentially make business rules practically self-enforcing; regardless of the application that touches the data.

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 number of trigger programs (note that there can be up to 6 per file - one per action)
The trigger program names and libraries
The trigger events
The trigger times
The trigger update conditions

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:

To ensure data values between files are kept in a state that meets the rules of their business. For example if a company has an order entry system, where each order has a header and a number of details; it makes no sense to add a detail if there is no header.
To be able to define the relationships between data values.
To have the system enforce the data relationships no matter what application makes changes.
To improve the performance of integrity checks done in a HLL or SQL level by moving the checking into the database.

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.

Bibliography

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.