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

출처: https://forums.oracle.com/forums/thread.jspa?threadID=466812

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