Triggers

A trigger is a set of actions that runs automatically when a specified change operation is performed on a specified table or view.

The change operation can be an SQL INSERT, UPDATE, or DELETE statement, or an insert, an update, or a delete high-level language statement in an application program. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail.

Triggers can be defined as SQL or external.

For an external trigger, the ADDPFTRG CL command is used. The program containing the set of trigger actions can be defined in any supported high level language. External triggers can be insert, update, delete, or read triggers.

For an SQL trigger, the CREATE TRIGGER statement is used. The trigger program is defined entirely using SQL. SQL triggers can be insert, update, or delete triggers. An SQL trigger can also be defined to have more than one of these events within a single trigger program.

Once a trigger is associated with a table or view, the trigger support calls the trigger program whenever a change operation is initiated against the table or view, or any logical file or view created over the table or view. SQL triggers and external triggers can be defined for the same table. Only SQL triggers can be defined for a view. Up to 300 triggers can be defined for a single table or view.

Each change operation for a table can call a trigger before or after the change operation occurs. Additionally, you can add a read trigger that is called every time the table is accessed. Thus, a table can be associated with many types of triggers.

  • Before delete trigger
  • Before insert trigger
  • Before update trigger
  • After delete trigger
  • After insert trigger
  • After update trigger
  • Read-only trigger (external trigger only)

Each change operation for a view can call an instead of trigger which will perform some set of actions instead of the insert, update, or delete. A view can be associated with an:

  • Instead of delete trigger
  • Instead of insert trigger
  • Instead of update trigger