sal_check

CREATE OR REPLACE
TRIGGER sal_check_ct
FOR INSERT OR UPDATE OF job, sal ON emp
compound TRIGGER

    temp_sal emp.sal%TYPE;

BEFORE each ROW IS
BEGIN
    CASE
    WHEN inserting OR updating
    THEN
        SELECT MAX(sal) INTO temp_sal FROM emp WHERE job = :NEW.job;
        IF (temp_sal < :NEW.sal) THEN
            raise_application_error (-20002,'error - Gehalt ist zu hoch!');
      END IF;

    SELECT MIN(sal) INTO temp_sal FROM emp WHERE job = :NEW.job;

    IF (temp_sal > :NEW.sal) THEN
      raise_application_error (-20002, 'error - Gehalt zu niedrig!');
    END IF;
    END CASE;
END BEFORE each ROW;

END sal_check_ct;