多個事件 SQL 觸發程式

可以定義觸發程式來處理一個、兩個或全部三個插入、更新及刪除事件。 唯一的限制是事件必須共用一般 BEFORE、AFTER 或 INSTEAD OF 觸發時間。 觸發程式事件述詞可以在觸發程式主體內使用，以識別導致觸發程式啟動的事件。

在下列範例中，數個警告 SQLSTATE 分組為單一觸發程式定義。

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;