-- maxraise.sql

CREATE OR REPLACE TRIGGER max_raise
 BEFORE UPDATE OF pay_amount
     ON employee
    FOR EACH ROW
DECLARE
  limit NUMBER(9,2);
BEGIN
  IF :new.pay_type_code IN ('H', 'T') THEN
    limit := 20;
  ELSIF :new.pay_type_code = 'S' THEN
    limit := 10000;
  ELSIF :new.pay_type_code = 'C' THEN
    limit := 5000;
  END IF;

  IF :new.pay_amount - :old.pay_amount > limit THEN
    raise_application_error(-20001,
     'Pay increase exceeds limit ' || limit
     || ' for pay type code ' || :new.pay_type_code);
  END IF;
END;
/


