091 - [Oracle PL/SQL] Trigger - Mutating & ON DELETE CASCADE

PK 설정으로 인한 mutating 이 발생하는 경우에 대해서 알아보겠습니다.

 

 

• 테스트를 위한 테이블 생성 - 부모 테이블

drop table DPET_parent;
/
CREATE TABLE DPET_parent
(DEPTNO NUMBER,
 DNAME VARCHAR2(20),
 CONSTRAINT DPET_parent_PK PRIMARY KEY (DEPTNO ) 
 );
 
INSERT INTO DPET_parent (DEPTNO,DNAME)
VALUES (1,'HR DEPT');
INSERT INTO DPET_parent (DEPTNO,DNAME)
VALUES (2,'PO DEPT');
COMMIT;

SELECT  * FROM DPET_parent;
-------------------------------
    DEPTNO DNAME               
---------- --------------------
         1 HR DEPT             
         2 PO DEPT

 

 

 

• 테스트용 테이블 생성 - 자식 테이블

drop table EMP_child;
/

-- "ON DELETE CASCADE" - 마스터 테이블은 DPET_parent, 차일드 테이블은 EMP_child 이며,
-- 마스터 테이블에서 데이터를 지우면 연결된 차일드 테이블의 row도 같이 삭제됨.
CREATE TABLE EMP_child
( EMPID NUMBER PRIMARY KEY,
  ENAME VARCHAR2(20),
  DEPTNO NUMBER,
  CONSTRAINT EMP_child_FK FOREIGN KEY (DEPTNO) REFERENCES DPET_parent(DEPTNO) ON DELETE CASCADE
);

INSERT INTO EMP_child VALUES (1, 'khaled','1');
INSERT INTO EMP_child VALUES (2, 'ali','1');
INSERT INTO EMP_child VALUES (3, 'ahmed','1');
INSERT INTO EMP_child VALUES (4, 'rania','2');
INSERT INTO EMP_child VALUES (5, 'lara','2');
COMMIT;

SELECT * FROM EMP_child;
------------------------------------
     EMPID ENAME                    DEPTNO
---------- -------------------- ----------
         1 khaled                        1
         2 ali                           1
         3 ahmed                         1
         4 rania                         2
         5 lara                          2

 

 

 

부모테이블과 자식테이블 사이에는 외부키로 연결이 되어 있다.

자식 테이블의 외부키가 부모테이블의 PK로 지정되어 있다.

여기서 특이 사항은 자식 테이블의 외부키 선언에서 "ON DELETE CASCADE" 옵션을 사용하였다.

옵션의 의미는 부모테이블에서 삭제된 키를 자식 테이블에서 참조할 경우, 관련된 자식테이블의 row 도 같이 삭제된다.

 

• 테스트 결과

DELETE FRO과M DPET_parent
WHERE DEPTNO=1;

SELECT * FROM EMP_child;
------------------------------
     EMPID ENAME                    DEPTNO
---------- -------------------- ----------
         1 khaled                        1
         2 ali                           1
         3 ahmed                         1
         4 rania                         2
         5 lara                          2


-- 삭제 전후의 쿼리 결과


     EMPID ENAME                    DEPTNO
---------- -------------------- ----------
         4 rania                         2
         5 lara                          2

 

 

 

• Mutating 상황을 만드는 트리거 생성

--EMP_child 테이블에 삭제가 발생하면 동작하는 트리거
create or replace trigger EMP_child_t
before
delete
on EMP_child
for each row
declare
minv number;
begin
    select min(EMPID)
    into minv
    from EMP_child;
end;

 

 

 

• 테스트 결과 오류 발생

-- 마스터 테이블에서 삭제를 시도하면 차일드 테이블에 연결된 데이터도 삭제를 시도한다
-- 하지만 차일드 테이블의 트리거가 삭제되려는 데이터에 접근하므로 mutating 오류가 발생하여
-- 아래 쿼리는 오류가 발생하며 종료된다.
DELETE FROM DPET_parent
WHERE DEPTNO=2;
------------------------------------------
Error report -
SQL Error: ORA-04091: table HR.EMP_CHILD is mutating, trigger/function may not see it
ORA-06512: at "HR.EMP_CHILD_T", line 4
ORA-04088: error during execution of trigger 'HR.EMP_CHILD_T'
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.