-- maxraise.sql

CREATE OR REPLACE TRIGGER emp_max_raise
   BEFORE UPDATE OF pay_amount ON employee
   FOR EACH ROW
DECLARE
   diff NUMBER;
BEGIN
   IF :new.pay_type_code in ('T','H') THEN
     diff := 20;
   ELSIF :new.pay_type_code = 'S' THEN
     diff := 10000;
   ELSIF :new.pay_type_code = 'C' THEN
     diff := 5000;
   END IF; 
   IF :new.pay_amount > :old.pay_amount + diff THEN
     raise_application_error(-20001, 'Pay increase too much-- It has ' ||
          'exceeded the limit of ' || diff || '.');
   END IF;
END;
/

UPDATE employee
   SET pay_amount = pay_amount * 1.5
 WHERE id = (SELECT manager_id FROM store
              WHERE store_number = 7);
