Embedded SQL Programming Guide

Trigger Event

Every trigger is associated with an event. Triggers are activated when their corresponding event occurs in the database. This event occurs when the specified triggering SQL operation, 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 that the trigger new_hire is activated when an insert operation is performed on table employee.

Every event, and consequently, every trigger, can be associated with exactly one subject table and exactly one triggering operation. The triggering 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 triggering SQL operation 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, will be activated only if an update operation is performed 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 ]