Specifying what makes a trigger fire (triggering statement or event)

Every trigger is associated with an event. Triggers are activated when their corresponding event occurs in the database. This trigger event occurs when the specified action, either an UPDATE, INSERT, or DELETE statement (including those caused by actions of referential constraints), is performed on the target table.

About this task

For example:

    CREATE TRIGGER NEW_HIRE
      AFTER INSERT ON EMPLOYEE 
      FOR EACH ROW  
      UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1

The preceding statement defines the trigger new_hire, which activates when you perform an insert operation on table employee.

You associate every trigger event, and consequently every trigger, with exactly one target table and exactly one modify operation. The modify operations are:

Insert operation: An insert operation can only be caused by an INSERT statement or the insert operation of a MERGE statement. Therefore, triggers are not activated when data is loaded using utilities that do not use INSERT, such as the LOAD command.
Delete operation: A delete operation can be caused by a DELETE statement, or the delete operation of a MERGE statement, or as a result of a referential constraint rule of ON DELETE CASCADE.
Update operation: An update operation can be caused by an UPDATE statement, or the update operation of a MERGE statement, or as a result of a referential constraint rule of ON DELETE SET NULL.

If the trigger event is an update operation, the event can be associated with specific columns of the target table. In this case, the trigger is only activated if the update operation attempts to update any of the specified columns. This provides a further refinement of the event that activates the trigger.

For example, the following trigger, REORDER, activates only if you perform an update operation on the columns ON_HAND or MAX_STOCKED, of the table PARTS:

    CREATE TRIGGER REORDER
      AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
      REFERENCING NEW AS N_ROW
      FOR EACH ROW 
      WHEN (N_ROW.ON_HAND < 0.10 * N_ROW.MAX_STOCKED)
      BEGIN ATOMIC
      VALUES(ISSUE_SHIP_REQUEST(N_ROW.MAX_STOCKED -
                                    N_ROW.ON_HAND,
                                    N_ROW.PARTNO));
      END

When a trigger is activated, it runs according to its level of granularity as follows:

FOR EACH ROW: It runs as many times as the number of rows in the set of affected rows. If you need to refer to the specific rows affected by the triggered action, use FOR EACH ROW granularity. An example of this is the comparison of the new and old values of an updated row in an AFTER UPDATE trigger.
FOR EACH STATEMENT: It runs once for the entire trigger event.

If the set of affected rows is empty (that is, in the case of a searched UPDATE or DELETE in which the WHERE clause did not qualify any rows), a FOR EACH ROW trigger does not run. But a FOR EACH STATEMENT trigger still runs once.

For example, keeping a count of number of employees can be done using FOR EACH ROW.

    CREATE TRIGGER NEW_HIRED 
      AFTER INSERT ON EMPLOYEE 
      FOR EACH ROW 
      UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1

You can achieve the same affect with one update by using a granularity of FOR EACH STATEMENT.

    CREATE TRIGGER NEW_HIRED 
      AFTER INSERT ON EMPLOYEE 
      REFERENCING NEW_TABLE AS NEWEMPS 
      FOR EACH STATEMENT
      UPDATE COMPANY_STATS 
      SET NBEMP = NBEMP + (SELECT COUNT(*) FROM NEWEMPS)

Note:

A granularity of FOR EACH STATEMENT is not supported for BEFORE triggers.
The maximum nesting level for triggers is 16. That is, the maximum number of cascading trigger activations is 16. A trigger activation refers to the activation of a trigger upon a triggering event, such as insert, update, or delete of data in a column of a table, or generally to a table.