In our everyday lives, we all need help getting things done. Whether this help comes in the form of an alarm clock to wake us in the morning, a personal organizer to help us remember our schedules and tasks, or a snow blower to aid us in removing snow, we use tools everyday to accomplish things. Sometimes, however, just having the tools isn’t enough. We want those tools to do things for us, without our intervention. To do that, we sometimes use one tool to trigger an event from another tool. Maybe, in addition to your alarm clock, you have a coffeemaker that automatically brews a pot of coffee at a specific time. Maybe your electronic personal organizer can be triggered to page or email someone at a certain time or at a specific stock quote. As for the snow blower, it’s difficult to trigger it to remove the snow for you, but you can trigger the neighborhood kid to snow blow your driveway whenever it snows.

Event triggers not only save us time and energy but also remove the stress of remembering to get things done. And, in some cases, this can add a layer of consistency to our lives. (Although this is not necessarily the case with the neighborhood snow-blower kid, who can actually add stress and inconsistency to your life!)

T-R-I-double-Guh-ER

As IT professionals, programs are the tools that make our lives easier. We write programs, users run those programs, and magical stuff happens. But sometimes we write too many programs or write different versions of the same program. Other times we duplicate routines in multiple programs. To make things worse, when we rewrite the same routines, they are not always consistent with previous versions. And sometimes 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. This makes it extremely difficult and dangerous to add logic that is dependent on existing logic. So it seems we could all use triggers in our IT lives, too. Well, never fear. The DB2 UDB trigger is here.

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. A trigger program can be written in a high-level language (HLL) such as RPG, COBOL, or C; in SQL; or in CL. Trigger programs are associated with physical files. As implemented in DB2 UDB, they cannot be assigned to individual fields. Furthermore, trigger events can

only be associated with adding a record, changing a record, or deleting a record. It is possible to assign a trigger to occur before and/or after any of those three events. Therefore, you can assign between zero and six triggers to any given physical file. 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 an HLL application. Triggers are either in place or not in place; there is no “sometimes” when working with triggers. That’s right, you don’t have to worry if the trigger is going to be late or run over your mailbox with the snow blower. A trigger is automatic and does exactly what you tell it.

The Wonderful Thing About Triggers

So what can you tell triggers to do? Pretty much anything you would do with an application program can be accomplished with a trigger. We hesitate to mention any because we don’t want to limit your imagination, but tasks such as data validation, user notification, and subsequent file updating can all be accomplished by using triggers. Archiving data for backups or audit trails is another task that fits quite well inside a trigger program. Although it is a separate function, not related directly to the trigger function, the journal process on the AS/400 is trigger-like in that it is kicked off when something happens to a physical file. So you may be more familiar with triggers than you thought.

Whatever use you employ, the beauty of triggers is that they are stored in a database. Once they are written and added to the appropriate 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. The fact that triggers fire without human intervention helps make applications bulletproof.

You Can Tell a Trigger by Its Stripes

There are two steps to implementing triggers. The first step is to write a trigger program. This can be accomplished using your HLL of choice on the AS/400. (You can also use CL and SQL.) 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 to 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 being modified.

Figure 1, Section A shows a breakdown of the trigger buffer. As you can see, there is plenty of information at your disposal. You are not required to use any of it, but you must at least code for it. Then, depending on your specific application, you can use what you need.

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). Figure 2 (page 40) illustrates the ADDPFTRG parameters. 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, or *UPDATE. 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.

What Triggers Do Best

Now let’s check out what a trigger program looks like. It won’t be much different from any other application you write to conduct business rules or validate data. The primary differences are that your trigger program requires two parameters to represent the trigger buffer section and its length. These parameters are passed into 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. Figure 1 contains a shortened version of the program, from which almost all the comments and much white space, both vertical and horizontal, have been removed. You can download the heavily documented version from the Midrange Computing Web site, at www.midrangecomputing.com/mc. This program will fire after an update, insert, or delete is performed on the order detail file. Section A in Figure 1 shows the data structure used to obtain the trigger buffer information. Parm_01 is the data structure passed as the first parameter and contains the trigger buffer section. Parm_02 is the second parameter passed to the program and contains the length of the trigger buffer section in Parm_01. Section B shows how we use information contained in the trigger buffer section to extract other information and to tell us what to do with that information. In this code, we need to find the order number and extended dollar amount so that we can update the order header file. Section C shows how we take the data from the order detail file and update the order header file.

There is no method for defining a field-level trigger. If you want to fire a trigger when a certain field changes, you must make the trigger program determine when that field changes value and then carry out the necessary processing. The purpose of this trigger program is to update the order header file with changed order data. Since the only data we are really interested in is the dollar amount, we require the trigger to fire only when the extended dollar amount changes. Keep in mind that this 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 specifically only when a change in the extended dollar amount was detected, we will make a little change to our program to accomplish just that.

The way to approach this is to keep the same trigger definitions. 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 quite simple and straightforward 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.

Because it is possible for a trigger program to be called repeatedly in a very short amount of time, the developer must consider what approach to take with respect to *INLR and other factors such as opening and closing files used by the trigger program. These types of job start and job end operations are relatively expensive with regard to machine performance because of the cost of memory allocation, program entry, and file opening, but that must also be weighed against the problem of locked objects. When deciding whether to shut down the trigger program or close the files, there is no one correct answer. Job start-up expense, including the opening and closing of files, must be looked upon as any other application program when considering performance.

What Triggers Do Worst

As we have mentioned, a trigger program is associated with a physical file through the OS/400 command ADDPFTRG. If you display the file description of a physical file, you will see information indicating whether or not any trigger programs are attached to the file

and, if so, the names of the trigger programs and library where each trigger program is found. It is important to understand that creating the trigger program and attaching the program to a physical file are two separate steps. However, it is necessary for the trigger program be created first, before it is assigned as the trigger. If a physical file has a trigger definition and there is an attempt to modify the file when the program cannot be found, the user will receive an escape message, CPF4160, indicating that the add, change, or delete operation failed. Therefore, the developer must make sure the program exists in the library specified on the ADDPFTRG command.

Additional 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 both FILE1 and the copy of FILE1. On the other hand, if 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; we only mention it because, when duplicating files with trigger definitions, an operator must make sure the trigger is either attached or not attached to the new file, as desired.

Trigger programs can also significantly impact performance. Keep in mind that the trigger is firing as part of the operating system’s database functionality. No matter what process writes, changes, or deletes a record in a file, the triggers will be fired. Whenever an application writes a record to 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 take care to keep the trigger program streamlined and make sure it is written in the most efficient manner in order to improve overall performance. (This is not a bad idea in all development cases.)

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 dispatch an entry onto the data queue and return control to the application that requested the file I/O. The application can continue processing while the trigger processing continues in a separate job.

Because of the performance implications, use caution when including expensive operations like Submit Job (SBMJOB) in the trigger program. Such a design is suitable for a physical file where records are infrequently added, changed, or deleted. If there is a large block of record operations that will result in repeated triggers, an alternate design might improve performance.

Finally, given the unconditional nature of firing a trigger program, it is important to remember the impact of triggers when performing operations that might have been innocuous in the past. An example might be running a file purge to remove old records from a history file. You might not intend to have the delete trigger fire under these circumstances, even though you might want it in place for day-to-day tasks. Also, if you perform the Clear Physical File (CLRPF) command on a file with a delete trigger associated with it, it will fail with a CPF3157 error message.

Triggers Are Wonderful Things!

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. 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. While triggers won’t help get the neighborhood kid to your house on time to clear your driveway, DB2 triggers will allow you to relax a little as you fire up the snow blower and head for his mailbox.

FORDHDR UF A E K DISK

DParm_01 DS
D*-(Static Section = 96 bytes)
D File_Name 1 10 Physical File Name
D Lib_Name 11 20 File Library
D Mbr_Name 21 30 Member Name
D Trg_Event 31 31 Trigger Event
D* 1=Insert Record 2=Delete Record 3=Update Record
D Trg_Time 32 32 Trigger Time
D* 1=After Add/Delete/Update 2=Before Add/Delete/Update
D Commit_Lck 33 33 Commit Lock Level
D CCSID 37 40B 0 CCSID
D RRN 41 44B 0 Relative Rcd Nbr
D Filler_01 45 48 Not Used
D Old_Offset 49 52B 0 Offset to Org Rcd
D Old_RcdLen 53 56B 0 Length of Org Rcd
D Old_NBMOff 57 60B 0 NullByteMap Offset
D Old_NBMLen 61 64B 0 NullByteMap Length
D New_Offset 65 68B 0 Offset to New Rcd
D New_RcdLen 69 72B 0 Length of New Rcd
D New_NBMOff 73 76B 0 NullByteMap Offset
D New_NBMLen 77 80B 0 NullByteMap Length
D Filler_02 81 96
D*-(Variable Section = at least 2x (rcd len + number of fields)
D* old and new record images and null-byte maps.
D Data 8192

DParm_02 DS
D Buffer_Len 4B 0

* before and after record images
DOld_ORDDTL E DS ExtName(ORDDTL) Prefix(O_)
DNew_ORDDTL E DS ExtName(ORDDTL) Prefix(N_)

DInsert S LIKE(Trg_Event) INZ('1')
DDelete S LIKE(Trg_Event) INZ('2')
DUpdate S LIKE(Trg_Event) INZ('3')
DAfter S LIKE(Trg_Time ) INZ('1')
DBefore S LIKE(Trg_Time ) INZ('2')

C *Entry PList
C Parm Parm_01
C Parm Parm_02

C* Is this an INSERT, DELETE, or UPDATE?
C Select
C Trg_Event WhenEQ Insert
C Eval New_ORDDTL = %subst(Data: C New_Offset-95: New_RcdLen)
C ExSR $Add

C Trg_Event WhenEQ Delete
C Eval Old_ORDDTL = %subst(Data:
C Old_Offset-95: Old_RcdLen)
C ExSR $Remove

C Trg_Event WhenEQ Update
C Eval Old_ORDDTL = %subst(Data:
C Old_Offset-95: Old_RcdLen)
C Eval New_ORDDTL = %subst(Data:
C New_Offset-95: New_RcdLen)
C*
C* If UPDATE, did key field value change?
C If (O_ODORD# = N_ODORD#)
C Eval N_ODEAMT = N_ODEAMT-O_ODEAMT
C Else
C ExSR $Remove
C EndIf
C*
C ExSR $Add
C EndSL

C Return

C* If ORDHDR record exists, add the extended amount from ORDDTL
C* to total amount (OHTOTL), else add new record to ORDHDR
C $Add BegSR
C N_ODORD# Chain FORDHDR 99
C If *IN99
C Reset FORDHDR
C Eval OHORD# = N_ODORD#
C Eval OHTOTL = N_ODEAMT
C Write FORDHDR
C Else
C Eval OHTOTL = OHTOTL + N_ODEAMT
C Update FORDHDR
C EndIf
C EndSR

C* If ORDHDR rec exists, subtract ORDDTL amt from total value
C $Remove BegSR
C O_ODORD# Chain FORDHDR 99
C If Not(*IN99)
C Eval OHTOTL = OHTOTL - O_ODEAMT
C Update FORDHDR
C EndIf
C EndSR

B

C

A

B

Figure 1: A trigger program can update one file whenever a user updates another.

Figure 2: The ADDPFTRG command adds intelligence to your database.