Commands, statements, and operations that you cannot use in trigger programs

 

A trigger program cannot include some commands, statements, and operations.

The system returns an exception if you use these:

• The commitment definition associated with the insert, update, delete, or read operation that called the trigger does not allow the COMMIT operation. The COMMIT operation is allowed for any other commitment definition in the job.

• The commitment definition associated with the insert, update, delete, or read operation that called the trigger does not allow the ROLLBACK operation. The ROLLBACK operation is allowed for any other commitment definition in the job.

• The SQL CONNECT, DISCONNECT, SET CONNECTION, and RELEASE statements are not allowed.

• The commitment definition associated with the insert, update, delete, or read operation that called the trigger does not allow the ENDCMTCTL CL command. The ENDCMTCTL CL command is allowed for any other commitment definition in the job.

• An attempt to add a local API commitment resource (QTNADDCR) to the same commitment definition associated with the insert, update, delete, or read operation that called the trigger.

• An attempt to do any input/output operation to a file that a trigger program has opened with *SHARE and is the file that caused the trigger program to be called.

• The called trigger program that uses the same commitment definition as the insert, update, delete, or read operation that called the trigger and that already has an existing remote resource. However, the system puts the entire transaction into a rollback-required state:

  • If the trigger program fails and signals an escape message AND

  • Any remote resource was updated during the nonprimary commit cycle for either a system other than System i™ or for the system that is at a pre-Version 3 Release 2 level.

  • The trigger program can add a remote resource to the commitment definition associated with the insert, update, delete, or read operation that called the trigger. This allows for LU62 remote resources (protected

  • conversation) and DFM remote resources (DDM file open), but not DRDA^® remote resources.

  • If a failure occurs when changing a remote resource from a trigger program, the trigger program must end by signalling an escape message. This allows the system to ensure that the entire transaction, for all remote locations, properly rolls back. If the trigger program does not end with an escape message, the databases on the various remote locations might become inconsistent.

  • A commit lock level of the application program is passed to the trigger program. Run the trigger program under the same lock level as the application program.

  • The trigger program and application program can run in the same or different activation groups. Compile the trigger program with ACTGRP(*CALLER) to achieve consistency between the trigger program and the application program.

  • A trigger program calls other programs or it can be nested (that is, a statement in a trigger program causes the calling of another trigger program). In addition, a trigger program itself can call a trigger program. The maximum trigger nested level for insert, update, delete, or read is 200. When the trigger program runs under commitment control, the following situations will result in an error:

    • Any update of the same record that has already been changed by the change operation or by an operation in the trigger program.

    • Conflicting operations on the same record within one change operation. For example, the change operation inserts a record, then the record is deleted by the trigger program.
    Notes:

    1. If the change operation is not running under commitment control, the system always protects the change operation. However, the system does not monitor updating the same record within the trigger program.

    2. The ALWREPCHG(*NO|YES) parameter of the Add Physical File Trigger (ADDPFTRG) command controls repeated changes under commitment control. Changing from the default value to ALWREPCHG(*YES) allows the same record or updated record associated with the trigger program to repeatedly change.

  • The Allow Repeated Change ALWREPCHG(*YES) parameter on the Add Physical File Trigger (ADDPFTRG) command also affects trigger programs defined to be called before insert and update database operations. If the trigger program updates the new record in the trigger buffer and ALWREPCHG(*YES) is specified, the actual insert or update operation on the associated physical file uses the modified new record image. This option can be helpful in trigger programs that are designed for data validation and data correction. Because the trigger program receives physical file record images (even for logical files), it can change any field of that record image.

  • The trigger program is called for each row that is changed in or read from the physical file.

  • If the physical file or the dependent logical file is opened for insert SEQONLY(*YES) processing, and the physical file has an insert trigger program associated with it, the system changes the open to SEQONLY(*NO) so it can call the trigger program for each row that is inserted.

 

Parent topic:

Precautions to take when coding trigger programs