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

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

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.

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)