MUTATING ERROR(ORA-04091)

TRIGGER 사용 시 MUTATING ERROR(ORA-04091) 해결책

==============================================

 

1. MUTATING ERROR란 무엇인가?

 

어느 TABLE에 DML(INSERT, UPDATE, DELETE 등)이 실행될 때마다 프로그램에

구애받지 않고 특정 작업을 수행하려할 때 database trigger를 사용한다.

예)EMP table에 data insert, update, delete 시 부서별 평균 급여 table에

updating 하는 경우.

 

이 경우 trigger를 사용하지 않고 같은 작업을 하려면 평균 급여를 구하는

PL/SQL program을 개발하여 EMP 테이블에 action이 발생 시마다 call하여 사용

하든가, 아니면 각 action 발생 후 동일한 routine을 반복 수행시켜야 한다.

 

이 때 만일 user가 EMP table에 update 시마다 EMP table에 어떤 처리를 수행

하는 trigger를 만든다면 원치 않는 결과를 일으킬 수 있고 OS memory가 소진

될 때까지 trigger가 trigger를 recursive하게 fire시켜 마치 looping

program과 같은 상황을 초래할 수도 있다.

 

이러한 trigger를 recursive trigger라 부르며 이런 불상사를 막기 위해

ORACLE은 EMP table에 row trigger를 만들어 원천적으로 trigger 내에서

EMP table을 아예 access 할 수 없도록 하고 있고, 이와 같은 원칙에 위배될

경우 발생되는 error를 mutating error 라고 부른다.

 

이 경우 user가 trigger를 만든 후 DML(insert, update, delete)을 수행 시

"ORA-4091:table SCOTT.EMP is mutating, trigger/function may not see

it." 와 같은 error를 만나게 된다.

 

2. ERROR가 발생하는 조건.

 

TRIGGER에는 다음과 같은 두 종류가 있다.

*row trigger - 프로그램에서 한 row 단위로 처리 시 처리할 때마다

fire되는 trigger.

 

*statement trigger - 프로그램 당 한번만 fire되는 trigger.

 

위와 같으므로 만일 application에서 한 row만 처리한다면 두 type에는 차이가

없고 여러 row를 처리할 경우 두 type 간의 차이가 발생한다.

 

Statement trigger는 일부 제한은 있으나 원칙적으로 mutating error를 발생

시키지 않는다.

Row trigger는 하나의 row 처리 후 해당 table에 대한 계속된 row 처리가 있을

수 있으므로 작업이 완료되기까지 해당 table을 access하는 것이 금지되지만

statement trigger는 일단 하나의 statement가 완료되었다는 보장을 할 수

있으므로 mutating의 기본 속성인 "현재 변화되고 있는 table" 이라는 범위에

들지 않는다.

따라서, mutating error는 row trigger에서의 제한 사항이라 해도 무리가 없다.

 

3. 해결 방법.

 

위에서 보았 듯 mutating error를 피해 나가려면 statement trigger를 사용하면

어려움이 없으나 statement trigger에서는 row trigger에서와 같이 row 단위로

변경 전 후 column data를 handling할 수 없다는 단점이 있다.

즉 :new.column, :old.column을 사용하지 못한다.

 

이와 같은 문제로 인하여 row trigger를 사용 시는 temp table 이나 PL/SQL

table을 이용하여 피해갈 수가 있다.

 

다음은 row trigger를 사용 시 mutating error를 유발하는 case(A)와

이를 statement trigger로 전환하여 error를 피해가는 case(B)

에 대한 내용이다.

 

예) EMP table에 insert, update, delete 시 부서별 평균 급여를 계산하여

DEPT table에 load한다. (TABLE COLUMN은 다음과 같다.)

 

SQL> desc emp

Name Null? Type

------------------------------- -------- ----

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

 

 

SQL> desc dept

Name Null? Type

------------------------------- -------- ----

DEPTNO NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SAL NUMBER(7,2)

 

(CASE A) ROW Trigger 만을 사용 시 에러가 발생하는 case.

 

1) row trigger 생성

 

create or replace trigger emp_aft_row_trigger

after insert or update or delete on emp

for each row

declare

v_sal emp.sal%type;

begin

select avg(sal) into v_sal from emp

where deptno=:old.deptno;

 

update emp

set sal=v_sal

where deptno=:old.deptno;

 

if :old.deptno != :new.deptno then

select avg(sal) into v_sal from emp

where deptno=:new.deptno;

 

update emp

set sal=v_sal

where deptno=:new.deptno;

end if;

end;

/

 

2) DATA 1건을 UPDATE한다.

 

SQL)update emp

set sal=10000

where empno= 7934;

SQL)

update emp

*

ERROR at line 1:

ORA-04091: table JMKIM.EMP is mutating, trigger/function may not

see it

ORA-06512: at line 4

ORA-04088: error during execution of trigger 'JMKIM.EMP_AFT_ROW_TRIGGER'

 

(CASE B) 에러를 피해 가는 방법

 

1) PL/SQL table을 생성한다.

 

SQL) 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;

/

 

Package created.

 

 

2) BEFORE STATEMENT trigger를 생성한다.

 

SQL)create or replace TRIGGER emp_bef_stm_all

before insert or update or delete on emp

begin

emp_pkg.emp_index :=0;

end;

/

SQL)

Trigger created.

 

3) AFTER ROW trigger를 생성한다.

 

SQL>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;

/

SQL>Trigger created.

 

4) AFTER STATEMENT trigger를 생성한다.

 

SQL>create or replace TRIGGER emp_aft_stm_all

after insert or update or delete on emp

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;

/

 

SQL>

Package created.

 

5) data insert 및 확인

 

SQL> update emp

set sal = 9000

where empno=7902;

 

SQL>

DEPTNO(old)=>20

1 row updated.