Application Development Guide

Trigger 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 (including those caused by actions of referential constraints), is performed on the subject table. For example:

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

The above 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 subject table and exactly one update operation. The update operations are:

Insert operation

An insert operation can only be caused by an INSERT statement. Therefore, triggers are not activated when data is loaded using utilities that do not use INSERT, such as the LOAD command.

Update operation

An update operation can be caused by an UPDATE statement or as a result of a referential constraint rule of ON DELETE SET NULL.

Delete operation

A delete operation can be caused by a DELETE statement or as a result of a referential constraint rule of ON DELETE CASCADE.

If the trigger event is an update operation, the event can be associated with specific columns of the subject 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 MODE DB2SQL 
      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 

[ DB2 List of Books | Search the DB2 Books ]