Multiple event SQL triggers

A trigger can be defined to handle one, two, or all three of the insert, update, and delete events. The only restriction is that the events must share a common BEFORE, AFTER, or INSTEAD OF triggering time. Trigger event predicates can be used within the trigger body to distinguish which event caused the trigger to be activated.

In the following example, several warning SQLSTATEs are grouped into a single trigger definition.

CREATE TABLE PARTS (INV_NUM INT, PART_NAME CHAR(20), ON_HAND INT, MAX_INV INT, 
                          PRIMARY KEY (INV_NUM))


CREATE OR REPLACE TRIGGER INVENTORY_WARNINGS
  AFTER DELETE OR INSERT OR UPDATE OF ON_HAND ON PARTS
  REFERENCING NEW AS N_INV 
              OLD AS O_INV
  FOR EACH ROW MODE DB2SQL
  BEGIN
    IF INSERTING THEN
      IF (N_INV.ON_HAND > N_INV.MAX_INV) THEN
        BEGIN
         SIGNAL SQLSTATE '75001' ('Inventory on hand exceeds maximum allowed.');
        END;
      END IF;
    ELSEIF UPDATING THEN
      IF (N_INV.ON_HAND < 5) THEN
        BEGIN
          SIGNAL SQLSTATE '75002' ('Inventory low - Re-order soon.');
        END;
      END IF;
    ELSEIF DELETING THEN
      IF (O_INV.ON_HAND > 0) THEN
        BEGIN
          SIGNAL SQLSTATE '75004' ('Deleting a part while inventory still in stock.');
        END;
      END IF;
    END IF;
  END

CREATE TABLE TRANSACTION_HISTORY(INV_NUM INT, POSTTIME TIMESTAMP, 
                                      TRANSACTION_TYPE CHAR(1))

CREATE TRIGGER SET_TRANS_HIST
  AFTER INSERT OR UPDATE OR DELETE ON PARTS
  REFERENCING NEW AS N 
              OLD AS O
  FOR EACH ROW MODE DB2SQL
  BEGIN
    INSERT INTO TRANSACTION_HISTORY VALUES (
      CASE
        WHEN INSERTING OR UPDATING
          THEN N.INV_NUM
        WHEN DELETING 
          THEN O.INV_NUM
        END,
      CURRENT TIMESTAMP,
      CASE 
        WHEN INSERTING
          THEN 'I'
        WHEN UPDATING
          THEN 'U'
        WHEN DELETING
          THEN 'D'
      END
    );
  END