090 - [Oracle PL/SQL] Trigger - Mutating(compound & array)

Mutating 오류가 발생하는 경우는 많이 있습니다.

이번에는 모든 부서별 월급 상/하한선을 감시하는 트리거를 만들어 보겠습니다.

저장할 값이 많아서 배열을 이용하여 저장하는 방식으로 구현하였습니다.

 

 

• 준비단계

-- 준비 단계, 복사본 테이블을 만들어서 테스트 합니다
drop table emp_copy
/
create table emp_copy
as select * from employees;


--복사된 테이블 확인
select EMPLOYEE_ID, FIRST_NAME,HIRE_DATE, JOB_ID,SALARY
from emp_copy
where job_id='IT_PROG'
order by salary;
---------------------------------------
EMPLOYEE_ID FIRST_NAME           HIRE_DATE JOB_ID         SALARY
----------- -------------------- --------- ---------- ----------
        107 Diana                07-FEB-07 IT_PROG          4200
        105 David                25-JUN-05 IT_PROG          4800
        106 Valli                05-FEB-06 IT_PROG          4800
        104 Bruce                21-MAY-07 IT_PROG          6000
        103 Alexander            03-JAN-06 IT_PROG          9005

 

 

 

• 각 부서별 상/하한선은 아래와 같음

--각 부서별 월급 하한선/상한선 확인
select job_id, min(salary),max(salary)
from emp_copy
group by job_id
order by 1;
------------------------------------

JOB_ID     MIN(SALARY) MAX(SALARY)
---------- ----------- -----------
AC_ACCOUNT        8300        8300
AC_MGR           12008       12008
AD_ASST           4401        4401
....
SH_CLERK          2500        4200
ST_CLERK          2100        3600
ST_MAN            5800        8200

19 rows selected.

 

 

 

• 이렇게 트리거 만들면 안됩니다

-- min/max값을 벗어나면 오류가 발생하는 트리거 예시(mutating 발생가능)
create or replace trigger salary_range
before insert OR UPDATE on emp_copy
for each row
declare
    v_min_sal number;
    v_max_sal number;
begin
   select min(salary), max(salary)
   into v_min_sal,v_max_sal
   from emp_copy
   where job_id=:new.job_id;
   
   if :new.salary not between v_min_sal and v_max_sal then
   raise_application_error (-20300,'invalid range');
   end if;
end;

 

 

 

• 오류 발생 확인 - mutating 발생

-- 이전 블로그에서 발생한 동일한 오류 발생(mutating)
UPDATE emp_copy
SET SALARY=6000
WHERE employee_ID=107;
-----------------------------------------
Error report -
SQL Error: ORA-04091: table HR.EMP_COPY is mutating, trigger/function may not see it
ORA-06512: at "HR.SALARY_RANGE", line 5
ORA-04088: error during execution of trigger 'HR.SALARY_RANGE'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

 

 

 

• 배열을 사용하여 저장하는 트리거 샘플

create or replace trigger salary_range
for insert OR UPDATE on emp_copy
compound trigger
	-- 배열선언
    type job_t is record( minsal number,maxsal number);
    type emp_t is table of job_t index by varchar2(20);
    emp emp_t;
    tmp varchar2(100);
    before statement is
    begin
    -- 배열에 정보 저장
        for i in(
                select job_id, min(salary) min_sal ,max(salary) max_sal
                from emp_copy
                group by job_id order by 1
                )
        loop
            emp(i.job_id).minsal:=i.min_sal;
            emp(i.job_id).maxsal:=i.max_sal;
        end loop;
    end before statement;
   
   before each row is
   begin
        if :new.salary not between emp(:new.job_id).minsal and emp(:new.job_id).maxsal then
            tmp:=('invalid range, yours['||:new.salary||'], min['||emp(:new.job_id).minsal||'], max['||emp(:new.job_id).maxsal||']');
            raise_application_error (-20300,tmp);
        end if;
   end before each row;
   
end;

 

 

 

• 정상동작하는 테스트 결과

select EMPLOYEE_ID, FIRST_NAME,HIRE_DATE, JOB_ID,SALARY
from emp_copy
where Job_id='IT_PROG'
order by salary;
---------------------------------------------
EMPLOYEE_ID FIRST_NAME           HIRE_DATE JOB_ID         SALARY
----------- -------------------- --------- ---------- ----------
        107 Diana                07-FEB-07 IT_PROG          4200
        105 David                25-JUN-05 IT_PROG          4800
        106 Valli                05-FEB-06 IT_PROG          4800
        104 Bruce                21-MAY-07 IT_PROG          6000
        103 Alexander            03-JAN-06 IT_PROG          9005


UPDATE emp_copy
SET SALARY=100
WHERE employee_ID=107;
---------------------------------------------
Error report -
SQL Error: ORA-20300: invalid range, yours[100], min[4200], max[9005]
ORA-06512: at "HR.SALARY_RANGE", line 24
ORA-04088: error during execution of trigger 'HR.SALARY_RANGE'


UPDATE emp_copy
SET SALARY=5500
WHERE employee_ID=107;


select EMPLOYEE_ID, FIRST_NAME,HIRE_DATE, JOB_ID,SALARY
from emp_copy
WHERE employee_ID=107;
---------------------------------------------
EMPLOYEE_ID FIRST_NAME           HIRE_DATE JOB_ID         SALARY
----------- -------------------- --------- ---------- ----------
        107 Diana                07-FEB-07 IT_PROG          5500