Midrange Programmer

In IT, we write programs, users run those programs, and magical stuff happens, right? Well, sometimes we write too many programs or write different versions of the same program. We aren't able to keep track of where all the business rules are, whether these rules are consistent, and whether they are being run where they are supposed to be run. What can we do to get a handle on this situation? Resign? Yeah, that's one way, but another, less drastic, method is to use database triggers.

A database trigger is a predefined event that runs automatically whenever a specified action is performed on a physical file. A trigger program carries out this predefined event and can be written in your choice of high-level language, such as RPG, COBOL, C, SQL, or CL. A trigger program is associated with a physical file and can be assigned to run on a read, add, change, or delete operation against that physical file. It is possible to assign a trigger to occur before or after add, change, and delete events, but only after read events. You can assign up to 300 triggers to any given physical file, and as of release V5R1 of OS/400, once you add a trigger to a file, you can disable and enable that trigger again without physically removing and re-adding the trigger. The triggers are always in place, regardless of how that physical file is accessed. That means that maintaining a file through a logical file, an SQL statement, or a JDBC connection will fire a trigger just as if a physical file were opened by a high-level-language application.

Anything you can do with an application program can be accomplished with a trigger. Tasks such as data validation, user notification, and subsequent file updating are all prime candidates for triggers. Archiving data for backups or audit trails is another task that fits quite well inside a trigger program.

The advantage of triggers is that they are stored in a database. Once they are written and added to the appropriate physical file, you don't have to worry about them. They are processed by DB2. You don't have to add a call to every program that might use the trigger. Instead you let the database handle the call for you, when an operation occurs against a record within a file. This lends itself well toward bullet-proofing your applications because you are able to write the business rule once and ensure that it gets run every time it's required.

There are two steps to implementing triggers. The first step is to write a trigger program. This can be accomplished using your high-level language of choice on the AS/400. When an action is performed on a file that is to fire a trigger, DB2 passes two parameters to the trigger program: the trigger buffer section, which contains information about the change taking place in the record, and the length of the trigger buffer. This means that you have to account for those parameters inside your trigger program. The trigger buffer allows you to interrogate trigger information, file information, and the "before" and "after" data of the record or field being modified.

The second step, once the program is written, is to add it to your physical file. The command to do this is Add Physical File Trigger (ADDPFTRG). Most of the parameters are straightforward. You need to supply a file name and indicate how the trigger is to be defined. It is here that you tell whether the trigger is to fire *BEFORE or *AFTER the physical file action and whether that action is *INSERT, *DELETE, *UPDATE, *READ. You then provide the name of the trigger program. The rest of the parameters are optional. With these parameters, you can determine whether to replace an existing trigger, whether you want multiple record changes to occur within the trigger program, and under which conditions to fire the trigger event on an *UPDATE action. This latter parameter allows you to stop a trigger from being fired when a record is updated but none of the field values have changed. The last two parameters allow you to define whether the trigger program is threadsafe and how it is to run in a multithreaded environment.

Now let's check out what the trigger program RPGTRIGGER looks like. It won't be much different from any other RPG application you write. The primary difference is that your trigger program requires two parameters to represent the trigger buffer section and its length. These parameters are passed to the program automatically by OS/400. You will also need a data structure to house the trigger buffer section. The sample trigger program, which was written in RPG IV, updates total sales dollars in an order header file whenever an order detail record is updated.

This program will fire, or run, after an update, insert, or delete is performed on the order detail file. Parm_01 is the data structure passed as the first parameter, and it contains the trigger buffer section. Parm_02 is the second parameter passed to the program, and it contains the length of the trigger buffer section in Parm_01.

The purpose of this trigger program is to update the order header file with changed order detail data. Since the only data we are really interested in is the dollar amount, we need only care when that specific field changes. However, there is no automatic way to trigger on an individual field, so we will have to do it in our program. The approach still calls for the trigger program to be activated every time the record event (*UPDATE, *INSERT, or *DELETE, in our case) occurs, whether or not the extended dollar amount is altered by the operation. But since we want this trigger to execute only when a change in the extended dollar amount is detected, we will make a little change to our program to accomplish just that.

By referring to the data structures that are passed into the trigger program, the program has access to both the "before" and "after" images of the record. This makes it simple to compare the extended dollar amount in the new image with that of the old, to see if they match. If the two fields have the same value, simply perform a RETURN operation to immediately return control to the application. If, on the other hand, the values are different, the program can go on to process the logic required to update the order header file.

Caution must be taken when performing other operations on database files with triggers. For example, assume that FILE1 has a trigger program PRGA associated with it. If the Create Duplicate Object (CRTDUPOBJ) command is executed and a copy of FILE1 is made, the trigger definition goes with it. That is, the trigger program will be associated with FILE1 and the copy of FILE1. If, on the other hand, the Copy File (CPYF) command is executed with the Create File (CRTF) parameter set to *YES, another file will be created as expected, but that copy of FILE1 will not have the trigger information associated with it. Neither situation is necessarily right or wrong; I only mention it so that you will understand what's going on.

Trigger programs can also significantly impact performance. Keep in mind that the trigger is firing as part of the operating system's database function. Whenever an application performs an operation against a file, it will not receive notification that the disk I/O is complete until the trigger program returns control to the application that caused the trigger to fire. Therefore, you should consider designs that use asynchronous update methods. For example, if there is to be a considerable amount of processing once a trigger is fired, you might consider creating a batch "monitor" program that waits for entries to arrive on a data queue. The trigger program can then immediately send an entry to the data queue and return control to the application that requested the file I/O. Meanwhile, the processing that you wanted to accomplish can take place in a separate job without causing any delays with the database I/O.

Triggers are effective tools for increasing software quality and integrity. They allow you to write reusable code and have that code run only when it needs to run. They allow the human element to be removed from much of the process, which not only decreases the chance for errors but also allows more time for the humans to design and code the business rules. I have showed you how to add a trigger to a physical file and provided you with an example trigger program. This should be enough to get you started. In future articles, I'll provide more examples on both how to create and control triggers, as well as provide additional trigger program examples.