04098. "trigger '%s.%s' is invalid and failed re-validation"

Question

I would like to write a trigger which blocks deleting records from table and blocks decreasing salary on update. It works well as two seperate triggers, but I would like to merge it into one.

Here is my trigger after merging:

  CREATE OR REPLACE TRIGGER test
    BEFORE DELETE OR UPDATE
    ON emp
    FOR EACH ROW
    BEGIN
      IF UPDATING THEN
        IF :NEW.sal < :OLD.sal THEN
          raise_application_error(-20500, 'You cannot decrease emp salary');
        END IF;
      ELSE DELETING THEN
        raise_application_error(-20500, 'You cannot delete records from emp');
      END IF;
    END;

The problem is when I try to update or delete record I get an error:

04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    A trigger was attempted to be retrieved for execution and was
           found to be invalid.  This also means that compilation/authorization
           failed for the trigger

I think that there is a problem around this line - BEFORE DELETE OR UPDATE because as I know if I FOR EACH ROW statment I should write is as BEFORE UPDATE on sal, but then I don't know how to join BEFORE DELETE statment into this.

EDIT:
Problem was around ELSE statment. It should be changed to ELSIF

Answer 1

Your IF statement syntax is incorrect. It should be:

CREATE OR REPLACE TRIGGER test
  BEFORE DELETE OR UPDATE
  ON emp
  FOR EACH ROW
BEGIN
  IF UPDATING AND :NEW.sal < :OLD.sal THEN
    raise_application_error(-20500, 'You cannot decrease emp salary');
  ELSIF DELETING THEN
    raise_application_error(-20500, 'You cannot delete records from emp');
  END IF;
END test;

I also combined the two IF statements used in the UPDATING case into one to keep it simpler.

EDIT

In the case where you don't want to allow salaries to be decreased another possible solution is to quietly change the salary back to its original value, as in:

CREATE OR REPLACE TRIGGER test
  BEFORE DELETE OR UPDATE
  ON emp
  FOR EACH ROW
BEGIN
  IF UPDATING AND :NEW.sal < :OLD.sal THEN
    :NEW.sal := :OLD.sal;  -- restore original salary
  ELSIF DELETING THEN
    raise_application_error(-20500, 'You cannot delete records from emp');
  END IF;
END test;