Reviewing the trigger categories
When discussing the restrictions placed on trigger programs, it is important to remember the six types of triggers. The differences among them have to do with why and when they are called. Three record I/O events cause triggers to be called -- deletion, insertion, and update. In addition, a trigger can be configured so it is called either before or after the database carries out the I/O event. This produces the following six possible combinations:
1. Before delete
2. After delete
3. Before insert
4. After insert
5. Before update
6. After update
Also recall that the same trigger program can be attached to multiple files and/or to the same file multiple times as different types of triggers.
Four issues regarding trigger inter-operability with i5/OS functions
Some i5/OS (OS/400) commands affecting physical files may not work as designed when triggers are involved. Here are a few of the more common situations:
1. When an attempt is made to clear -- either directly or indirectly -- a member of a physical file that has a delete trigger attached to it, the operation will fail with escape message CPF3157. An example of a direct clear-member operation is the Clear Physical File Member (CLRPFM) command. An example of an indirect clear-member operation is the Copy File (CPYF) command when the *REPLACE option is specified for the MBROPT keyword/parameter.
1. A file's trigger information is not copied to the new file when executing a CPYF command with CRTFILE(*YES). That is, the resulting newly-created file will not have any trigger programs attached to it.
2. Delete triggers are not called when the associated file is deleted (e.g., using the DLTF command).
3. To duplicate a file and its associated trigger programs to another library, and have the duplicated trigger programs associated with the duplicated file, first duplicate all the trigger programs, and then duplicate the file to the same library as the duplicated trigger programs. The system will automatically perform the re-association. Note: The duplicated objects should be given the same names as the original objects.
Operations that are invalid or not recommended in trigger programs
The following operations are not valid in a trigger program:
- A commit or rollback operation for the record that caused the trigger to be called.
- The End Commitment Control (ENDCMTCTL) CL command for the commitment definition being used for the record that caused the trigger to be called.
- A trigger cannot update (or delete) the record that caused the trigger to be called.
- SQL Connect, Disconnect, Release, and Set Connection statements.
In addition, IBM recommends not using the following commands in a trigger program. The commands can be used, but caution should be exercised.
- Start Commitment Control (STRCMTCTL)
- Reclaim Spool Storage (RCLSPLSTG)
- Reclaim Resources (RCLRSC)
- Change System Library List (CHGSYSLIBL)
- Delete, Restore, or Save Licensed Program (DLTLICPGM, RSTLICPGM, or SAVLICPGM)
- Save Library (SAVLIB) command with *YES specified for the SAVACT keyword/parameter.
- Any command accessing a diskette or tape drive (because of long access times and the possibility that the wrong volume -- or no volume at all -- will be loaded).
- Any migration commands.
- Starting debug (a security exposure).
- Any commands related to remote job entry (RJE).
- Invoking/calling a program or CL command that results in an interactive entry screen (could reach lock resource limit).
In the next installment, we'll start our tour of three unique trigger programs. Stay tuned.
About the author: Ron Turull is editor of Inside Version 5. He has more than 20 years' experience programming for and managing AS/400-iSeries systems.