Trigger 실행시 Ora-04091:Mutation error 처리방법 


예를들어 emp Table에 Insert/Update/Delete 될때마다 부서별 평균급여 Table에 Update 한다고 가정했을때 이때 사용자가 emp Table에 변경이 가해 졌을때 emp Table에 어떠한 처리를 하는 Trigger를 만든다면 원치않은 결과가 나올수가 있겠죠.
그래서 Oracle은 emp Table에 row Trigger를 만들면 원천적으로 emp Table을 아예 Access할수 없게 하며 이와 같은 원칙에 위배될때 mutationg error가 발생하게 된다.

해결책은 Statement trigger로 만들던지 PL/SQL Table을 생성하여 해결하는 방법이 있다. Statement Trigger 인 경우에는 :new.column, :old.column의 사용은 불가능하다. 여기서는 후자로 해결해보자.

emp Table의 LayOut은 아래와 같다.
SQL>desc emp
  NAME                      NULL?            TYPE
  ------------------------------------------------
  empno
  ename
  job
  mgr
  sal
  deptno

SQL>desc dept
  NAME                      NULL?            TYPE
  ------------------------------------------------
  deptno
  dname
  loc
  sal

1. PL/SQL Table을 생성한다.
create or replace package emp_pkg as
  type  emp_tab_type is table of emp.deptno%type index by
        binary_integer;
  emp_old  emp_tab_type;
  emp_new  emp_tab_type;
  emp_index binary_integer;
end emp_pkg;

2. Before statement Trigger를 생성한다.
create or replace trigger emp_bef_stm_all
      before insert or update or delete on emp
begin
emp_pkg.emp_index  := 0;
end;
/

3. After row Trigger를 생성한다.
create or replace trigger emp_aft_row_all
      after insert or update or delete on emp
      for each row
begin
emp_pkg.emp_index    := emp_pkg.emp_index + 1;
emp_pkg.emp_old(emp_pkg.emp_index) := :old.deptno;
emp_pkg.emp_new(emp_pkg.emp_index) := :new.deptno;
end;
/

4. After Statement Trigger를 생성
create or replace trigger emp_aft_atm_all
      after insert or update or delete on emp
      for each row
declare
v_sal  emp.sal%type;
begin
for i in 1..emp_pkg.emp_index loop
    select avg(sal) into v_sal from emp
    where deptno = emp_pkg.emp_old(i);

    update dept set sal = v_sal
    where deptno = emp_pkg.emp_old(i);

    dbms_output.put_line('DEPTNO(old)=>'||to_char(emp_pkg.emp_old(i)));

    if emp_pkg.emp_new(i) != emp_pkg.emp_old(i) then
        select avg(sal) into v_sal from emp
        where deptno = emp_pkg.emp_new(i);

        update dept set sal = v_sal
        where deptno = emp_pkg.emp_new(i);

        dbms_output.put_line('DEPTNO(NEW)=>'||to_char(emp_pkg.emp_NEW(i)));
    end if;
end loop;
emp_pkg.emp_index := 0;
end;
/

5. 확인
SQL>update emp set sap = 90000
  where empno = 1111;

SQL>
  DEPTNO(old)=>20
  1 row updated.