Triggers

A trigger is a set of actions that are executed automatically whenever a specified event occurs to a specified base table. A trigger can be defined to be executed before the event or after the event. The event can be an insert, update, or delete operation.

The set of actions can include almost any operation allowed on the AS/400 system. A few operations are not allowed, such as:

• Commit or rollback (if the same commitment definition is used for the trigger actions and the triggering event)
• CONNECT, SET CONNECTION, DISCONNECT, and RELEASE statements

For a complete list of restrictions, see the Database Programming book.

Because the database manager automatically executes a trigger, if a set of actions must occur whenever a specified operation occurs, applications do not have to duplicate the actions in every application program that performs the operation. This can result in better programmer productivity, better consistency, and in the case of distributed applications, better performance.

The actions performed in the trigger are considered to be part of the operation that caused the trigger to be executed. Thus, when the isolation level is anything other than NC (No Commit) and the trigger actions are performed using the same commitment definition as the trigger event:

• The database manager ensures that the operation and the triggers executed as a result of that operation either all complete or are backed out. Operations that occurred prior to the triggering operation are not affected.
• The database manager effectively checks all constraints (except for a constraint with a RESTRICT delete rule) after the operation and the associated triggers have been executed.

A trigger has an attribute that specifies whether it is allowed to delete or update a row that has already been inserted or updated within the SQL statement that caused the trigger to be executed.

• If ALWREPCHG(*YES) is specified when the trigger is defined, then within an SQL statement:
  • The trigger is allowed to update or delete any row that was inserted or already updated by that same SQL statement. This also includes any rows inserted or updated by a trigger or referential constraint caused by the same SQL statement.
• If ALWREPCHG(*NO) is specified when the trigger is defined, then within an SQL statement:
  • A row can be deleted by a trigger only if that row has not been inserted or updated by that same SQL statement. If the isolation level is anything other than NC (No Commit) and the trigger actions are performed using the same commitment definition as the trigger event, this also includes any inserts or updates by a trigger or referential constraint caused by the same SQL statement.
  • A row can be updated by a trigger only if that row has not already been inserted or updated by that same SQL statement. If the isolation level is anything other than NC (No Commit) and the trigger actions are performed using the same commitment definition as the trigger event, this also includes any inserts or updates by a trigger or referential constraint caused by the same SQL statement.

A trigger may perform operations that cause other triggers to be fired. Also, operations performed in a trigger may cause referential constraint rules to be executed.

A trigger is not allowed on a table that contains a LOB column.

Triggers are defined using the ADDPFTRG (Add Physical File Trigger) CL command. Triggers are dropped using the RMVPFTRG (Remove Physical File Trigger) CL command. For more information, see the Database Programming book.