Add Physical File Trigger (ADDPFTRG)

The Add Physical File Trigger (ADDPFTRG) command adds a system trigger to a specified physical file. A trigger defines a program that is called with a delete, insert, update or read operation occurs for a file.

The trigger program can be specified to be called before or after a change operation occurs. The change operation can be an insert, update, delete or read operation through any interface. Change operations do not include clearing, initializing, moving, applying journal changes, removing journal changes, or changing end of data operations.

A maximum of 300 triggers can be added to one physical file. The trigger program to be called can be the same for each trigger or it can be a different program for each trigger.

An exclusive-no-read lock is held on the physical file when adding a trigger to that file. All logical files which are built over the physical file are also held with the exclusive-no-read lock.

Once a trigger is added to the physical file, all members of that specified file are affected by the trigger. When a change operation occurs on a member of the specified file, the trigger program is called. The trigger program is also called when a change operation occurs by way of either a dependent logical file or a Structured Query Language (SQL) view that is built over the physical file.

More information on the trigger program is in the Database category in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/.

Restrictions

• You must have read (*READ), object operational (*OBJOPR), and object alter (*OBJALTER) or object management (*OBJMGT) authorities to the physical file, execute (*EXECUTE) authority to the file library, and *EXECUTE authority to the trigger program and its library to use this command.
• You must have update (*UPD) and *OBJOPR authorities to the file if ALWREPCHG(*YES) has been specified.
• If the physical file or a dependent logical file or SQL view is opened in this or another job, a trigger cannot be added.
• While this command is running, neither the physical file nor any dependent logical files can be opened.
• The trigger program must be a program of object type *PGM. It cannot be an Integrated Language Environment (ILE) service program of object type *SRVPGM.
• This command is conditionally threadsafe. In multithreaded jobs, this command is not threadsafe for distributed files and fails for distributed files that use relational databases of type *SNA.
• Read triggers may not be added to SQL materialized query tables.

Parameters

Physical file (FILE)

Specifies the physical file to which this trigger program is added. The file must exist on the system.

This is a required parameter.

Qualifier 1: Physical file

name

Specify the name of the file to which the trigger program is added.

Qualifier 2: Library

*LIBL

All libraries in the library list for the current thread are searched until the first match is found.

*CURLIB

The current library for the job is searched. If no library is specified as the current library for the job, the QGPL library is used.

name

Specify the name of the library to be searched.

Trigger time (TRGTIME)

Specifies the time when the trigger program is called.

This is a required parameter.

*BEFORE

The trigger program is called before the change operation on the specified physical file.

*AFTER

The trigger program is called after the change operation on the specified physical file.

Trigger event (TRGEVENT)

Specifies the event (the change operation to the physical file) that calls the trigger program. Only one event can be specified for each command issued.

This is a required parameter.

*INSERT

An insert operation calls the trigger program.

Note: If the physical file is not read and write capable, the *INSERT value cannot be specified.

*DELETE

A delete operation calls the trigger program.

Notes:

• If the physical file is not read and delete capable, the *DELETE value cannot be specified.
• If the physical file has a referential constraint with a delete rule of CASCADE, the *DELETE value cannot be specified.

*UPDATE

An update operation calls the trigger program.

Notes:

• If the physical file is not read and update capable, the *UPDATE value cannot be specified.
• If the physical file is a dependent file which has a foreign key with a delete rule of SET NULL or SET DEFAULT, the *UPDATE value cannot be specified.

*READ

A read operation calls the trigger program.

Note: If the physical file is not read capable, the *READ value cannot be specified.

Program (PGM)

Specifies the program that is called when the specified event occurs on the physical file. The program must exist on the system and be of object type *PGM.

This is a required parameter.

Qualifier 1: Program

name

Specify the name of the program to be called when the specified event occurs on the specified physical file.

Qualifier 2: Library

*LIBL

All libraries in the library list for the current thread are searched until the first match is found.

*CURLIB

The current library for the job is searched. If no library is specified as the current library for the job, the QGPL library is used.

name

Specify the name of the library to be searched.

Note: The special values *LIBL and *CURLIB are the values of the job running when the trigger program is added.

Replace trigger (RPLTRG)

Specifies whether an existing trigger is replaced by the trigger to be added when the triggers have the same trigger event and trigger time.

*NO

The existing trigger is not replaced.

*YES

The existing trigger is replaced. If *GEN was specified for the trigger name and the time and event match a single entry, the trigger will be replaced. If a trigger name was specified and it matches an existing entry, the trigger will be replaced. If a trigger with the specified trigger name does not exist, the new trigger is added to the physical file.

Trigger (TRG)

Specifies the name of the trigger being added.

*GEN

The system generates a trigger name.

character-value

Specify the name of the trigger. The trigger name must be unique to the library. The trigger name is used to distinguish triggers with the same time and event values. You can specify a maximum of 128 characters without delimiters or 258 characters with quotation mark (") delimiters.

Note: The case is preserved when lowercase characters are specified.

Trigger library (TRGLIB)

Specifies the library for the trigger being added.

*FILE

The library for the file specified on the FILE parameter is used.

*CURLIB

The current library for the job is used. If no library is specified as the current library for the job, the QGPL library is used.

Note: The special value *CURLIB is the value of the job running when the trigger program is added.

name

Specify the name of the library to be used.

Allow Repeated Change (ALWREPCHG)

Specifies whether repeated changes to a record within a trigger or any subsequent trigger(s) called while the trigger program is running are allowed.

*NO

Repeated changes to a record within a trigger or any subsequent trigger(s) called are not allowed.

*YES

Repeated changes to a record within a trigger or any subsequent trigger(s) called are allowed.

Threadsafe (THDSAFE)

Specifies whether the trigger program is threadsafe. This is intended for documentation purposes only. It may be used in determining the MLTTHDACN value, but there is no direct relationship between the THDSAFE and MLTTHDACN keywords.

*UNKNOWN

The threadsafe status of the trigger program is not known.

*NO

The trigger program is not threadsafe.

*YES

The trigger program is threadsafe.

Multithreaded job action (MLTTHDACN)

Specifies the action to take when the trigger program is called in a multithreaded job. The THDSAFE attribute of the trigger program can be used in determining the action, however, there is no direct relationship between the THDSAFE and MLTTHDACN keywords.

*SYSVAL

Use the QMLTTHDACN system value to determine the action to take.

*MSG

Run the trigger program in a multithreaded job, but send a diagnostic message.

*NORUN

Do not run the trigger program in a multithreaded job. Send an escape message.

*RUN

Run the trigger program in a multithreaded job.

If you do use the THDSAFE value to determine the value for MLTTHDACN, please read the following recommendations:

• If the THDSAFE value is *NO, MLTTHDACN should be set to *NORUN.
• If the THDSAFE value is *UNKNOWN, MLTTHDACN should be set to *SYSVAL.
• If the THDSAFE value is *YES, MLTTHDACN should be set to *RUN.

Trigger update condition (TRGUPDCND)

Specifies the condition under which an update event calls the trigger program.

Note: This parameter applies only when *UPDATE is specified for the Trigger event (TRGEVENT) parameter.

*ALWAYS

The trigger program is called whenever a record is updated, whether or not a value changes.

*CHANGE

The trigger program is called only when a record is updated and a value is changed.

Examples

Example 1: Adding a Trigger for an Insert Event

ADDPFTRG   FILE(EMP)  TRGTIME(*AFTER)  TRGEVENT(*INSERT)
           PGM(LIB2/INSTRG)

This command adds a trigger with trigger program INSTRG in library LIB2 to the physical file named EMP. When an insert operation occurs on the EMP file, the program INSTRG is called after the insert operation. The library list (*LIBL) is used to find the file because the FILE value is not qualified by a library name.

Example 2: Setting Multiple Trigger Events to Call One Trigger Program

ADDPFTRG   FILE(EMP)  TRGTIME(*AFTER)  TRGEVENT(*INSERT)
           PGM(LIB2/INSTRG)
ADDPFTRG   FILE(EMP)  TRGTIME(*AFTER)  TRGEVENT(*UPDATE)
           PGM(LIB2/INSTRG)

These two commands add triggers to call the trigger program INSTRG in library LIB2 when an insert or update operation occurs on the EMP file.

Example 3: Adding a Trigger Only When an Update Event Changes Values

ADDPFTRG   FILE(EMP)  TRGTIME(*BEFORE)  TRGEVENT(*UPDATE)
           PGM(LIB2/UPDTRG)  TRGUPDCND(*CHANGE)

The trigger program UPDTRG in library LIB2 is called before a value for a field of a record in the EMP file changes during an update.

Example 4: Replacing an Existing Trigger

ADDPFTRG   FILE(EMP)  TRGTIME(*BEFORE)  TRGEVENT(*UPDATE)
           PGM(LIB2/NEWPGM)  RPLTRG(*YES)  TRGUPDCND(*CHANGE)

The trigger program NEWPGM being added to the file EMP has the same trigger time (*BEFORE) and trigger event (*UPDATE) as the trigger program UPDTRG that was added in Example 3. Therefore, the added trigger program NEWPGM replaces the existing trigger program UPDTRG.

Example 5: Replacing a Trigger with a Trigger for a Different Update Condition

ADDPFTRG   FILE(EMP)  TRGTIME(*BEFORE)  TRGEVENT(*UPDATE)
           PGM(LIB2/NEWPGM)  RPLTRG(*YES)  TRGUPDCND(*ALWAYS)

The trigger added in Example 4 that calls the trigger program NEWPGM only is the values are changed, is replace by a trigger that always calls the trigger program NEWPGM regardless of the values.

Error messages

*ESCAPE Messages

CPF32C6

Trigger operation not successful.