꿈꾸는 개발자, DBA 커뮤니티 구루비

트리거에서 mutating 에러(ORA-04091) 해결 방법

by 마농 [PL/SQL] 트리거 mutating ORA-04091 [2011.08.04 10:48:59]

트리거에서 mutating 에러(ORA-04091) 해결 방법

-- test 용 테이블 생성 --
CREATE TABLE a(coseq NUMBER PRIMARY KEY, coname VARCHAR2(30));
CREATE TABLE b(coseq NUMBER, item VARCHAR2(10), itemname VARCHAR2(30), flag NUMBER(1));
INSERT INTO a VALUES(1, '가회사');
INSERT INTO a VALUES(2, '나회사');
INSERT INTO a VALUES(3, '다회사');
COMMIT;

-- 0. 패키지에서 변경된 자료를 저장할 변수 정의 --
CREATE OR REPLACE PACKAGE pkg_test
IS
TYPE tp_coseq    IS TABLE OF b.coseq   %TYPE INDEX BY BINARY_INTEGER;
TYPE tp_item     IS TABLE OF b.item    %TYPE INDEX BY BINARY_INTEGER;
TYPE tp_itemname IS TABLE OF b.itemname%TYPE INDEX BY BINARY_INTEGER;
TYPE tp_gubun    IS TABLE OF VARCHAR2(1)     INDEX BY BINARY_INTEGER;
v_coseq    tp_coseq   ;
v_item     tp_item    ;
v_itemname tp_itemname;
v_gubun    tp_gubun   ;
i BINARY_INTEGER;
END;
/

-- 1. 문장-트리거 에서 의 DML 실행전 패키지 변수 초기화 --
CREATE OR REPLACE TRIGGER tr_test_1
BEFORE INSERT OR UPDATE OR DELETE ON b
BEGIN
pkg_test.i := 0;
END;
/

-- 2. 행-트리거 에서 패키지 변수에 변경된 값을 저장 --
CREATE OR REPLACE TRIGGER tr_test_2
BEFORE INSERT OR UPDATE OR DELETE ON b
FOR EACH ROW
WHEN (new.flag IS NULL OR (new.flag = old.flag AND old.flag <> 0) )
BEGIN
pkg_test.i := pkg_test.i + 1;
IF INSERTING THEN
    :new.flag := 1;
    pkg_test.v_coseq   (pkg_test.i) := :new.coseq   ;
    pkg_test.v_item    (pkg_test.i) := :new.item    ;
    pkg_test.v_itemname(pkg_test.i) := :new.itemname;
    pkg_test.v_gubun   (pkg_test.i) := 'I'          ;
ELSIF UPDATING THEN
    :new.flag := :new.flag + 1;
    pkg_test.v_coseq   (pkg_test.i) := :old.coseq   ;
    pkg_test.v_item    (pkg_test.i) := :old.item    ;
    pkg_test.v_itemname(pkg_test.i) := :new.itemname;
    pkg_test.v_gubun   (pkg_test.i) := 'U'          ;
ELSIF DELETING THEN
    pkg_test.v_coseq   (pkg_test.i) := :old.coseq   ;
    pkg_test.v_item    (pkg_test.i) := :old.item    ;
    pkg_test.v_gubun   (pkg_test.i) := 'D'          ;
END IF;
END;
/

-- 3. 문장-트리거 에서 의 DML 실행후 별도 DML 처리 --
CREATE OR REPLACE TRIGGER tr_test_3
AFTER INSERT OR UPDATE OR DELETE ON b
BEGIN
FOR i IN 1..pkg_test.i LOOP
    IF pkg_test.v_gubun(i) = 'I' THEN
      INSERT INTO b(coseq, item, itemname, flag)
      SELECT coseq
           , pkg_test.v_item(i)
           , pkg_test.v_itemname(i)
           , 1
        FROM a
       WHERE coseq <> pkg_test.v_coseq(i)
      ;
    ELSIF pkg_test.v_gubun(i) = 'U' THEN
      UPDATE b
         SET itemname = pkg_test.v_itemname(i)
           , flag     = flag + 1
       WHERE coseq <> pkg_test.v_coseq(i)
         AND item = pkg_test.v_item (i)
      ;
    ELSIF pkg_test.v_gubun(i) = 'D' THEN
      UPDATE b
         SET flag = 0
       WHERE coseq <> pkg_test.v_coseq(i)
         AND item = pkg_test.v_item (i)
      ;
      DELETE FROM b
       WHERE flag = 0
      ;
    END IF;
END LOOP;
END;
/

-- 테스트 --
SELECT * FROM b;

INSERT INTO b(coseq, item, itemname)
VALUES(1, 'a1', 'aa1')
;

UPDATE b
   SET itemname = 'bb1'
WHERE coseq = 2
   AND item = 'a1'
;

DELETE FROM b
WHERE coseq = 1
   AND item = 'a1'
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변