db2-trigger.sql

CREATE TRIGGER DB2INST1.OVERDRAFT
NO CASCADE BEFORE
UPDATE OF CHECKINGBALANCE
ON DB2INST1.ACCTTABLE
REFERENCING OLD AS OLDROW NEW AS NEWROW
FOR EACH ROW
MODE DB2SQL
WHEN (NEWROW.CHECKINGBALANCE < 0)
BEGIN ATOMIC
  DECLARE overage DECIMAL (7,2);
  SET overage = (NEWROW.CHECKINGBALANCE*-1);
  IF overage < OLDROW.SAVINGBALANCE
    THEN SIGNAL SQLSTATE '70001' ('Overdraft Protection Unsuccessful);
    ELSE SET NEWROW.SAVINGBALANCE = OLDROW.SAVINGBALANCE - overage,
             NEWROW.CHECKINGBALANCE = 0;
  END IF;
END;