[ Oracle - Trigger 생성시 multating 에러와 해결책]
친구 놈이 mutating에러가 난다고 해서 제 나름대로 자료를 찾아서 정리를 해보았습니다. 쓰는 방식이 잘못 되었더군요.
보통 default와 trigger를 구분해서 잘 쓰셔야 하는데 default는 그 column에 대해 언급이 없을때 그 값이 들어가고
trigger는 무슨 값이 들어 오던지 trigger에 기술된 내용이 들어가는 것입니다.
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 이나 PLSQL 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.
발주처 : http://www.koug.net/
