CREATE TRIGGER

The CREATE TRIGGER statement defines a trigger. You can create the following types of triggers:

INSERT BEFORE - The triggered-action is executed before any changes are made by an insert on the subject table.
UPDATE BEFORE - The triggered-action is executed before any changes are made by an udpate on the subject table.
DELETE BEFORE - The triggered-action is executed before any changes are made by a delete on the subject table.
INSERT AFTER - The triggered-action is executed after any changes are made by an insert on the subject table.
UPDATE AFTER - The triggered-action is executed after any changes are made by an update on the subject table.
DELETE AFTER - The triggered-action is executed after any changes are made by a delete on the subject table.

Example:

Create a trigger, REORDER, that invokes user-defined function ISSUE_SHIP_REQUEST to issue a shipping request whenever a parts record is updated and the on-hand quantity for the affected part is less than 10% of its maximum stocked quantity. User-defined function ISSUE_SHIP_REQUEST orders a quantity of the part that is equal to the part's maximum stocked quantity minus its on-hand quantity; the function also ensures that the request is sent to the appropriate supplier.

The parts records are in the PARTS table. Although the table has more columns, the trigger is activated only when columns ON_HAND or MAX_STOCKED are updated.

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

Repeat the scenario except use a fullselect instead of a VALUES statement to invoke the user-defined function. This example also shows how to define the trigger as a statement trigger instead of a row trigger. For each row in the transition table that evaluates to true for the WHERE clause, a shipping request is issued for the part.

CREATE TRIGGER REORDER
  AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
  REFERENCING NEW TABLE AS NTABLE
  FOR EACH STATEMENT MODE DB2SQL
  BEGIN ATOMIC
  SELECT ISSUE_SHIP_REQUEST(MAX_STOCKED - ON_HAND, PARTNO)
    FROM NTABLE
    WHERE ON_HAND < 0.10 * MAX_STOCKED;
END 

You can also use the VALUES statement to invoking a user-defined function from a trigger. Transition variables can be passed to the user-defined function.

Example:

Create an after trigger EMPISRT1 that invokes user-defined function NEWEMP when the trigger is activated. An
insert operation on table EMP activates the trigger. Pass transition variables for the new employee number, last
name, and first name to the user-defined function.

CREATE TRIGGER EMPISRT1
  AFTER INSERT ON EMPLOYEE 
  REFERENCING NEW AS N
  FOR EACH ROW
  MODE DB2SQL
  BEGIN ATOMIC
  VALUES ( NEWEMP(N.EMPNO, N.LASTNAME, N.FIRSTNAME));
  END 

For more information, see CREATE TRIGGER and VALUES in the SQL Reference topic in the Information CenterLink to Information center.