Advanced trigger applications -- part 2

Ron Turull
A trigger program is just like any other program with a few restrictions on what it can do. In part 1 of this series, we discussed one of these restrictions: the trigger program's parameter list. Since the system will be calling the trigger program, it's parameter list must be strictly coded. Once a trigger program has been successful called, it can do almost anything a non-trigger program can. In this installment, we'll discuss in detail the other things trigger programs must not and/or should not do.

More Information

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.

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.