Having trouble getting around ORA-04091 (mutating table) with trigger

user_8440895 0
Viewed 29

Lets say we have this TableA:

EMPNO EXPCODE EXPYEARS
1 EXP1 4
1 EXP4 4

The premise of this trigger is that whenever someone makes an update to Table A, EXP4 is not allowed to be less than EXP1.

My first iteration was something along the lines of:

BEFORE INSERT OR UPDATE ON TableA
FOR EACH ROW
DECLARE
  EXP NUMBER(7,2);
BEGIN
  IF (:NEW.EXPCODE = 'EXP1') THEN
    SELECT NVL(X.EXPYEARS,0) INTO EXP FROM TableA X WHERE X.EMPNO = :NEW.EMPNO AND X.EXPCODE = 'EXP4';
    IF (EXP < :NEW.EXPYEARS) THEN raise_application_error(-20010,'EXP4 CANNOT BE LESS THAN EXP1'); END IF;
  ELSIF (:NEW.EXPCODE = 'EXP4') THEN
    SELECT NVL(X.EXPYEARS,0) INTO EXP FROM TableA X WHERE X.EMPNO = :NEW.EMPNO AND X.EXPCODE = 'EXP1';
    IF (EXP > :NEW.EXPYEARS) THEN raise_application_error(-20010,'EXP4 CANNOT BE LESS THAN EXP1'); END IF;    
  END IF;  
END;

I've gone through several iterations after that to try and get around this issue but to no avail. Any help would be appreciated. Our company doesn't own the application using the database so I'm trying to make QoL changes on the database side.

oracle
1 Answers

You'd need to create a compound trigger (or separate row- and statement-level triggers).

create type empno_t as table of integer;

create or replace trigger check_exp
  for insert or update on tableA
  compound trigger
  
  l_empnos empno_t := new empno_t();
  
  after each row
  is
  begin
    l_empnos.extend;
    l_empnos( l_empnos.count ) := :new.empno;
  end after each row;
  
  after statement
  is
    l_invalid_empnos integer;
  begin
    select count(*)
      into l_invalid_empnos
      from tableA expcode1
     where expcode1.expcode = 'EXP1'
       and expcode1.empno MEMBER OF l_empnos
       and exists( select 1
                     from tableA expcode4
                    where expcode4.expcode = 'EXP4'
                      and expcode4.empno = expcode1.empno
                      and expcode4.expyears < expcode1.expyears );
                      
    if( l_invalid_empnos > 0 )
    then
      raise_application_error( -20001, 'EXP4 expyears > EXP1 expyears ');
    end if;
  end after statement;
end;

Here is a fiddle that shows the compound trigger working.

Justin_Cave_103970
Related