트리거에서 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’
;
awesomejude 