DB2 UDB's database trigger capability can offer great flexibility to our applications. For example, if you determine you need to add some processing logic when a new customer order is created, you can code that logic into a new program and attach that new program to the Order file. This is especially helpful when you cannot or do not want to modify existing code. It is also helpful when someone adds an order using methods that bypass our normal order entry programs.

With all this goodness, comes some danger as well. The danger comes in the form of programming logic that is "hidden" from view when you examine source code for the Order Entry application. Since the trigger program is attached at the database level and not within the standard business application programs, it can be stealthy.

Suppose I write a trigger program that runs a destructive set of commands when a powerful user updates a commonly used control file. The program would do nothing if a non-powerful user updates a record, but when a user with powerful authorities (like QSECOFR) updates a record, the damaging commands would run under the authority of the powerful user.

A trigger program can be written in any OS/400 or i5/OS language, such as RPG IV, Cobol, CL, C, or SQL. A trigger program is executed when an I/O event occurs for a file. For example, I can add a trigger program to the Order file to send a message to the customer service manager when a new order is added.

If I have a high level of authority to a database file (*OBJALTER, *OBJMGT, *READ, *OBJOPR), I can add a trigger program to the file using the CL command Add Physical File Trigger (ADDPFTRG) or the SQL command CREATE TRIGGER, or even through iSeries Navigator.

We need to be vigilant in investigating existing trigger programs and watching for newly created ones. The IBM-supplied CL command Print Trigger Programs (PRTTRGPGM) will list existing trigger programs, and it has a change reporting feature that lets you list only newly created triggers. The PRTTRGPGM command should be part of our normal system checkup routines and run regularly.