-- min_hourly_wage_err.sql

CREATE OR REPLACE TRIGGER minimum_hourly_wage
  BEFORE INSERT OR UPDATE OF pay_amount
      ON employee
     FOR EACH ROW
    WHEN (new.pay_type_code = 'H')
DECLARE
   min_wage NUMBER(9,2);
BEGIN
   SELECT minimum_wage INTO min_wage
     FROM pay_type WHERE pay_type_code = 'H';
   IF :new.pay_amount < min_wage THEN
      -- :new.pay_amount := min_wage;
      raise_application_error(-20002,
        'Hourly pay must meet or exceed minimum wage, ' || min_wage);
   END IF;
END;
/
