089 - [Oracle PL/SQL] Trigger - Mutating error

트리거를 사용하다 Mutating 오류가 발생하는 이유와 해결하는 방법에 대해서 알아보자.

 

 

 

오류가 발생하는 이유는 실행한 쿼리의 일부 필드가 트리거에서 실행하는 쿼리와 충돌이 발생할 경우이다.

다시 말하면 테이블이 수정되고 있는 상황에서 트리거가 같은 테이블을 접근하려 할때 발생한다.

 

예를 들어보면 신규 고객정보를 입력시, 고객 번호를 새로 만들어야 하는데,

시퀀스.nextval 을 사용하는 경우가 아니고 max(고객번호) + 1 같이, 기존의 고객번호의 최대값을 이용할 경우,

아래와 같이 트리거를 작성할 수 있다.

 

-- 준비 작업
drop table customers
/
create table customers
( cust_id number,
  cust_name varchar2(20)
);


-- customers 테이블을 감시, 삽입전에 고객번호의 최대값에 1을 더해서, 새로운 고객번호로 사용.
create or replace trigger customers_seq
before insert on customers
for each row
declare --use declare when you want define variables
    v_max_id number;
begin
    -- 최대값이 널이면 0 으로 변환
    select nvl(max(cust_id),0)
    into v_max_id
    from customers;
    v_max_id := v_max_id+1;

    :new.cust_id:= v_max_id;
end;

 

 

 

고객 정보를 1개씩 입력할때는 문제가 발생하지 않는다

 

--now let us try to insert a record
--데이터를 1개씩 입력할때는 문제없이 입력됨
insert into customers (cust_name) values ('oracle');
/
select * from customers;

insert into customers (cust_name) values ('Khaled');
/
select * from customers;
-------------------------------------
   CUST_ID CUST_NAME
---------- --------------------------
         1 oracle
         2 Khaled

 

 

 

여러 데이터를 한번에 처리할때는 오류가 발생한다. 이유는 테이블이 insert 에 의해서 수정이 진행되는 중에 트리거가 다시 해당 테이블에서 max값을 가져오기 위해서 접근하기 때문이다.

 

insert into customers (cust_name) 
select first_name 
from employees;
--ORA-04091: table HR.CUSTOMERS is mutating
--THE TRIGGER CODE QUERY SAME RECORD inserted 
-------------------------------------
/*
Error report -
SQL Error: ORA-04091: table HR.CUSTOMERS is mutating, trigger/function may not see it
ORA-06512: at "HR.CUSTOMERS_SEQ", line 5
ORA-04088: error during execution of trigger 'HR.CUSTOMERS_SEQ'
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.
*/

 

 

 

트리거를 compound 방식 트리거로 수정한다.

 

create or replace trigger customers_seq
for insert on customers
compound trigger
    v_max_id number;

    before statement is
    begin
        select nvl(max(cust_id),0)
        into v_max_id
        from customers;
    end before statement;
    
    before each row is
    begin
        v_max_id := v_max_id+1;
        :new.cust_id:= v_max_id;
    end before each row;
end;

 

 

 

테스트 결과 - 이전 트리거에서 발생한 문제가 해결되었다

 

-- 107개의 row가 추가되었다
insert into customers (cust_name) 
select first_name 
from employees;
-------------------------------------
107 rows inserted.


-- 기존 2개의 데이터와 추가된 107개의 데이터, 전체 109개의 데이터가 검색된다.
select * from customers;
   CUST_ID CUST_NAME           
---------- --------------------
         1 oracle              
         2 Khaled              
         3 Ellen               
         4 Sundar              
         5 Mozhe               
....
       107 Matthew             
       108 Jennifer            
       109 Eleni               

109 rows selected.

 

 

 

 

• 다른 예시

drop table emp_copy;
/
create table emp_copy
as select * from employees;


select * from emp_copy
where job_id='IT_PROG'
order by salary;

 

 

 

• 트리거 설명: IT부서의 신규/업데이트시, salary 값을 검사하여 현재의 최소/최대값을 벗어나면 입력/업데이트 금지

--트리거 설명: IT부서의 신규/업데이트시, salary 값을 검사하여 현재의 최소/최대값을 벗어나면 입력/업데이트 금지
create or replace trigger IT_PROG_range
before insert OR UPDATE on emp_copy
for each row
when (new.job_id='IT_PROG')
declare
    v_min_IT_PROG number;
    v_max_IT_PROG number;
begin
    select min(salary), max(salary)
    into v_min_IT_PROG,v_max_IT_PROG
    from emp_copy
    where job_id=:new.job_id;
    
    if :new.salary not between v_min_IT_PROG and v_max_IT_PROG then
        raise_application_error (-20300,'invalid range');
    end if;
end;

 

 

 

• 로직 확인 - 범위내/범위외 의 로직이 정상동작하는지 확인

--salary 값이 현재의 최소/최대값을 벗어나서 입력이 되지 않는 쿼리 샘플
insert into emp_copy
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,
SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
)
VALUES
(300,'NADIA','AHMED','TEST@GMAIL.COM',null,sysdate,'IT_PROG',
1500,0,null,90
);
------------------------------------
Error report -
SQL Error: ORA-20300: invalid range
ORA-06512: at "HR.IT_PROG_RANGE", line 11
ORA-04088: error during execution of trigger 'HR.IT_PROG_RANGE'



--salary 값이 현재의 최소/최대값 이내여서 입력이 되는 쿼리 샘플
insert into emp_copy
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,
SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
)
VALUES
(300,'NADIA','AHMED','TEST@GMAIL.COM',null,sysdate,'IT_PROG',
5500,0,null,90
);
------------------------------------
1 row inserted.



-- 결과 확인
select EMPLOYEE_ID, FIRST_NAME,HIRE_DATE JOB_ID,SALARY
from emp_copy where job_id='IT_PROG';
----------------------------------------
EMPLOYEE_ID FIRST_NAME           JOB_ID        SALARY
----------- -------------------- --------- ----------
        103 Alexander            03-JAN-06       9005
        104 Bruce                21-MAY-07       6000
        105 David                25-JUN-05       4800
        106 Valli                05-FEB-06       4800
        107 Diana                07-FEB-07       4200
        300 NADIA                03-MAY-24       5500

 

 

 

• 오류 원인 - 업데이트문과 트리거에서 충돌이 발생

--트리거의 select문과 DML문과 충돌이 발생한 경우임
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.IT_PROG_RANGE", line 5
ORA-04088: error during execution of trigger 'HR.IT_PROG_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.
*/

 

 

 

• 트리거 수정 - compound 트리거 방식으로 수정

--해결방법은 compound trigger를 사용하여 DML문이 실행되기 전에 트리거 쿼리 실행.
create or replace trigger IT_PROG_range
for insert OR UPDATE on emp_copy
when (new.job_id='IT_PROG')
compound trigger
    v_min_IT_PROG number;
    v_max_IT_PROG number;

    before statement is
    begin
        select min(salary), max(salary)
        into v_min_IT_PROG,v_max_IT_PROG
        from emp_copy
        where job_id='IT_PROG';
    end before statement;
    
    before each row is
    begin
        if :new.salary not between v_min_IT_PROG and v_max_IT_PROG then
            raise_application_error (-20300,'invalid range');
        end if;
    end before each row;
end;

 

 

 

• 테스트 결과 확인 1

-- DML실행전.
select EMPLOYEE_ID, FIRST_NAME,HIRE_DATE JOB_ID,SALARY
from emp_copy where employee_ID=107;
----------------------------------------
EMPLOYEE_ID FIRST_NAME           JOB_ID        SALARY
----------- -------------------- --------- ----------
        107 Diana                07-FEB-07       4200


UPDATE emp_copy
SET SALARY=6000
WHERE employee_ID=107;
----------------------------------------
1 row updated.


-- DML실행후.
select EMPLOYEE_ID, FIRST_NAME,HIRE_DATE JOB_ID,SALARY
from emp_copy where employee_ID=107;
----------------------------------------
EMPLOYEE_ID FIRST_NAME           JOB_ID        SALARY
----------- -------------------- --------- ----------
        107 Diana                07-FEB-07       6000

 

 

 

• 테스트 결과 확인 2

UPDATE emp_copy
SET SALARY=6000
where job_id='IT_PROG';
----------------------------------------
6 rows updated.


select EMPLOYEE_ID, FIRST_NAME,HIRE_DATE JOB_ID,SALARY
from emp_copy where job_id='IT_PROG';
----------------------------------------
EMPLOYEE_ID FIRST_NAME           JOB_ID        SALARY
----------- -------------------- --------- ----------
        103 Alexander            03-JAN-06       6000
        104 Bruce                21-MAY-07       6000
        105 David                25-JUN-05       6000
        106 Valli                05-FEB-06       6000
        107 Diana                07-FEB-07       6000
        300 NADIA                03-MAY-24       6000

6 rows selected.