Examples

Example 1: Create two triggers that track the number of employees that a company manages. The triggering table is the EMPLOYEE table, and the triggers increment and decrement a column with the total number of employees in the COMPANY_STATS table. The COMPANY_STATS table has the following properties:

    CREATE TABLE COMPANY_STATS
      (NBEMP INTEGER,
       NBPRODUCT INTEGER,
       REVENUE DECIMAL(15,0))

This example uses row triggers to maintain summary data in another table.

Create the first trigger, NEW_HIRE, so that it increments the number of employees each time a new person is hired; that is, each time a new row is inserted into the EMPLOYEE table, increase the value of column NBEMP in table COMPANY_STATS by 1.

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

Create the second trigger, FORM_EMP, so that it decrements the number of employees each time an employee leaves the company; that is, each time a row is deleted from the table EMPLOYEE, decrease the value of column NBEMP in table COMPANY_STATS by 1.

    CREATE TRIGGER FORM_EMP
      AFTER DELETE ON EMPLOYEE
      FOR EACH ROW MODE DB2SQL
      BEGIN ATOMIC
        UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1;
      END 

Example 2: 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 eliminates any duplicate requests to order the same PARTNO and sends the unique order to the appropriate supplier.

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 

Example 3: Assume that table EMPLOYEE contains column SALARY. Create a trigger, SAL_ADJ, that prevents an update to an employee's salary that exceeds 20% and signals such an error. Have the error that is returned with an SQLSTATE of 75001 and a description. This example shows that the SIGNAL SQLSTATE statement is useful for restricting changes that violate business rules.

    CREATE TRIGGER SAL_ADJ
      AFTER UPDATE OF SALARY ON EMPLOYEE
      REFERENCING OLD AS OLD_EMP
                  NEW AS NEW_EMP
      FOR EACH ROW MODE DB2SQL
      WHEN (NEW_EMP.SALARY > (OLD_EMP.SALARY *1.20))
      BEGIN ATOMIC
        SIGNAL SQLSTATE '75001'('Invalid Salary Increase - Exceeds 20%');
      END