(오라클트리거,Oracle학원/PLSQL학원)Trigger Mutating Table Exception, Compound Tr…

FSP 0 166 2018.12.31 17:47

(오라클트리거,Oracle학원/PLSQL학원)Trigger Mutating Table Exception, Compound Triggers 

 

 

http://ojc.asia

Trigger Mutating Table Exception, Compound Triggers

 

n  Mutating Table Exception  : 행레벨 트리거(Row-Level Trigger)에서 변하고 있는 값을 참조하거나 변경하려고 할 때 발생되는 오류(ORA-04091: table XXX is mutating, trigger/function may not see it)

n  Mutating 에러는 Insert, Update, Delete와 같은 DML이 수행되고 여기에 연결되어 있는 행레벨 트리거에서 변하는 값을 참조하는 경우 발생하는 오류이다.

n  Mutating 에러는 Compund Trigger를 이용하여 해결할 수 있는데 하나의 트리거에 여러 개의 트리거링 타이밍을 기술하려면 COMPOUND TRIGGER구를 사용한다.

n  개별 트리거링 타이밍에서 밖에서 선언한 전역 변수에 대해 참조가 가능하므로 행 레벨 트리거에서 변하는 값들을 전역 변수(주로 배열처럼 쓰이는 테이블 타입의 컬렉션 변수)에 저장한 후 명령문 레벨 트리거의 AFTER 타이밍에서 한번에 처리함으로써 트리거에서 변하고 있는 값을 참조하는 경우 자주 발생하는 ORA-04091 에러를 방지할 수 있다.

 

[Compond Trigger 형식]

 

CREATE OR REPLACE TRIGGER 컴파운드_트리거이름

  FOR 트리거링 이벤트 ON 테이블명

    COMPOUND TRIGGER

  -- 전역 변수 선언

  g_global_variable VARCHAR2(10);

 

  BEFORE STATEMENT IS   --명령문 레벨 트리거 BEFORE

  BEGIN

    NULL; -- Do something here.

  END BEFORE STATEMENT;

 

  BEFORE EACH ROW IS  --행레벨 트리거 BEFORE

  BEGIN

    NULL; -- Do something here.

  END BEFORE EACH ROW;

 

  AFTER EACH ROW IS  --행레벨 트리거 AFTER

  BEGIN

    NULL; -- Do something here.

  END AFTER EACH ROW;

 

  AFTER STATEMENT IS   --명령문 레벨 트리거 AFTER

  BEGIN

    NULL; -- Do something here.

  END AFTER STATEMENT;

 

END <trigger-name>;

/

 

 

1. 행레벨 트리거가 변하는 값을 참조하는 경우

 

CREATE OR REPLACE TRIGGER emp_trigger

AFTER INSERT ON emp

FOR EACH ROW  -- 행레벨 트리거

DECLARE

  cnt NUMBER;

BEGIN

  SELECT count(*)

  INTO cnt

  FROM emp;

 

  dbms_output.put_line('전체 사원수 : ' || cnt);

END;

/

 

SQL> insert into emp (empno, ename) values (3333,'8길동');

insert into emp (empno, ename) values (3333,'8길동')

            *

1행에 오류:

ORA-04091: SCOTT.EMP 테이블이 변경되어 트리거/함수가 볼 수 없습니다.

ORA-06512: "SCOTT.EMP_TRIGGER",  4

ORA-04088: 트리거 'SCOTT.EMP_TRIGGER'의 수행시 오류

 

2. ORA-04091을 방지하기 위해 명령문 레벨 트리거로 변경

 

CREATE OR REPLACE TRIGGER emp_trigger

AFTER INSERT ON emp

-- FOR EACH ROW  -- 행레벨 트리거

DECLARE

  cnt NUMBER;

BEGIN

  SELECT count(*)

  INTO cnt

  FROM emp;

 

  dbms_output.put_line('전체 사원수 : ' || cnt);

END;

/

 

SQL> insert into emp (empno, ename) values (3333,'8길동');

전체 사원수 : 17

 

1 개의 행이 만들어졌습니다.

SQL> rollback;

 

3. pragma autonomous transaction 을 이용

:: DML이 일어나는 세션의 트랜잭션과는 별개로 동작

 

CREATE OR REPLACE TRIGGER emp_trigger

AFTER INSERT ON emp

FOR EACH ROW  -- 행레벨 트리거

DECLARE

  PRAGMA AUTONOMOUS_TRANSACTION;

  cnt NUMBER;

BEGIN

  SELECT count(*)

  INTO cnt

  FROM emp;

 

  dbms_output.put_line('전체 사원수 : ' || cnt);

END;

/

 

-- 트리거을 일으킨 트랜잭션이 종료되지 않아 입력된 건은 반영되지 않는다.

SQL> select count(*) from emp;

  COUNT(*)

----------

        17

SQL> insert into emp (empno, ename) values (4444,'9길동');

전체 사원수 : 17

 

1 개의 행이 만들어졌습니다.

 

SQL> rollback;

 

4. Compound Trigger 이용

 

CREATE OR REPLACE TRIGGER emp_trigger

FOR INSERT or UPDATE

ON emp

COMPOUND TRIGGER

 

   /* Declaration Section*/

   cnt NUMBER;

 

  -- 행 레벨

  AFTER EACH ROW IS

  BEGIN

     dbms_output.put_line('Insert/Update is Completed');

  END AFTER EACH ROW;

 

  -- 명령문 레벨

  AFTER STATEMENT IS

  BEGIN

    SELECT count(*)

    INTO cnt

    FROM emp;

   

    dbms_output.put_line('전체 사원수 : ' || cnt);

   END AFTER STATEMENT;

 

END emp_trigger;

/

 

SQL> select count(*) from emp;

 

  COUNT(*)

----------

        18

 

SQL> insert into emp (empno, ename) values (7777,'7길동');

Insert/Update is Completed

전체 사원수 : 19

 

1 개의 행이 만들어졌습니다.

SQL> rollback;

 

--------------------------------------------------------------------------------

-- Mutating Table Exception 예제

--------------------------------------------------------------------------------

 

SQL> CREATE TABLE tr_test2 (

      id           NUMBER(10) primary key,

      name         VARCHAR2(50)

    );

 

테이블이 생성되었습니다.

 

SQL> CREATE SEQUENCE tr_test2_seq;

 

시퀀스가 생성되었습니다.

 

SQL> CREATE TABLE tr_test2_log (

      id            NUMBER(10) primary key,

      action        VARCHAR2(10) NOT NULL,

      tr_test2_id   NUMBER(10),

      count         NUMBER(5),

      cr_date       TIMESTAMP

    );

 

테이블이 생성되었습니다.

 

SQL> ALTER TABLE tr_test2_log ADD (

      CONSTRAINT tr_test2_fk FOREIGN KEY (tr_test2_id)

      REFERENCES tr_test2(id)

    );

 

테이블이 변경되었습니다.

 

SQL> CREATE SEQUENCE tr_test2_log_seq;

 

시퀀스가 생성되었습니다.

 

-- 패키지 명세

SQL> CREATE OR REPLACE PACKAGE tr_test2_pkg IS

      PROCEDURE tr_test2_change (p_id      IN  tr_test2.id%TYPE,

                                 p_action  IN  VARCHAR2);

    END tr_test2_pkg;

    /

 

패키지가 생성되었습니다.

 

-- 패키지 바디

SQL> CREATE OR REPLACE PACKAGE BODY tr_test2_pkg IS

      PROCEDURE tr_test2_change (p_id      IN  tr_test2.id%TYPE,

                                 p_action  IN  VARCHAR2)

      IS

        cnt  NUMBER(5) := 0;

      BEGIN

        SELECT COUNT(*)

        INTO   cnt

        FROM   tr_test2;

 

       INSERT INTO tr_test2_log

       VALUES (tr_test2_log_seq.NEXTVAL, p_action, p_id, cnt, SYSTIMESTAMP);

     END tr_test2_change;

   END tr_test2_pkg;

   /

 

패키지 본문이 생성되었습니다.

 

SQL> CREATE OR REPLACE TRIGGER tr_test2_trigger

      AFTER INSERT OR UPDATE ON tr_test2

      FOR EACH ROW

    BEGIN

      IF inserting THEN

        tr_test2_pkg.tr_test2_change(p_id => :new.id, p_action => 'INSERT');

      ELSE

        tr_test2_pkg.tr_test2_change(p_id => :new.id, p_action => 'UPDATE');

      END IF;

   END;

   /

 

트리거가 생성되었습니다.

 

-- tr_test2 after insert 트리거가 동작을 하는데 자기자신 테이블의 레코드건수를 count(*)

-- 변하는 값을 참조하므로 4091오류 발생

SQL> INSERT INTO tr_test2 (id, name) VALUES (tr_test2_seq.NEXTVAL, 'OJC');

INSERT INTO tr_test2 (id, name) VALUES (tr_test2_seq.NEXTVAL, 'OJC')

                                        *

1행에 오류:

ORA-04091: SCOTT.TR_TEST2 테이블이 변경되어 트리거/함수가 볼 수 없습니다.

ORA-06512: "SCOTT.TR_TEST2_PKG",  7

ORA-06512: "SCOTT.TR_TEST2_TRIGGER",  3

ORA-04088: 트리거 'SCOTT.TR_TEST2_TRIGGER'의 수행시 오류

 

[해결책]

 

-- 행 레벨 트리거와 명령문 트리거를 조합하여 해결

-- tr_test2_pkg 패키지에 명령문 트리거에서 호출할 프로시저 추가

-- PL/SQL 데이터 타입중 TABLE TYPE(배열처럼 쓰이는 것)을 이용하여 행 단위 트리거에서

-- 발생하는 데이터를 저장 후 명령문 트리거에서 일괄적으로 삽입하여 해결

SQL> CREATE OR REPLACE PACKAGE tr_test2_pkg AS

      PROCEDURE tr_test2_change (p_id      IN  tr_test2.id%TYPE,

                                 p_action  IN  VARCHAR2);

      PROCEDURE tr_test2_statement_change;

    END tr_test2_pkg;

    /

 

패키지가 생성되었습니다.

 

SQL> CREATE OR REPLACE PACKAGE BODY tr_test2_pkg AS

      TYPE type_change_rec IS RECORD (

        id      tr_test2.id%TYPE,

        action  tr_test2_log.action%TYPE

      );

 

      TYPE type_change_tab IS TABLE OF type_change_rec;

      g_change_tab  type_change_tab := type_change_tab();

 

     PROCEDURE tr_test2_change (p_id      IN  tr_test2.id%TYPE,

                                p_action  IN  VARCHAR2)

     IS

     BEGIN

       g_change_tab.extend;

       g_change_tab(g_change_tab.last).id     := p_id;

       g_change_tab(g_change_tab.last).action := p_action;

     END tr_test2_change;

 

     PROCEDURE tr_test2_statement_change IS

       cnt  NUMBER(10);

     BEGIN

       FOR i IN g_change_tab.first .. g_change_tab.last LOOP

         SELECT COUNT(*)

         INTO   cnt

         FROM   tr_test2;

 

         INSERT INTO tr_test2_log

         VALUES (tr_test2_log_seq.NEXTVAL, g_change_tab(i).action,

                 g_change_tab(i).id, cnt, SYSTIMESTAMP);

       END LOOP;

       g_change_tab.delete;

     END tr_test2_statement_change;

   END tr_test2_pkg;

   /

패키지 본문이 생성되었습니다.

 

-- 새로 추가한 프러시저(tr_test2_statement_change)

-- 호출하기 위한 명령문 레벨 트리거를 추가하자.

SQL> CREATE OR REPLACE TRIGGER tr_test2_trigger2

    AFTER INSERT OR UPDATE ON tr_test2

    BEGIN

      tr_test2_pkg.tr_test2_statement_change;

    END;

    /

트리거가 생성되었습니다.

SQL> INSERT INTO tr_test2 (id, name) VALUES (tr_test2_seq.NEXTVAL, 'OJC');

1 개의 행이 만들어졌습니다.

 

------------------------------------------------------------------------------------------

앞에서 작성한 Mutating Table Exception Compound Trigger를 이용해결

------------------------------------------------------------------------------------------

SQL> drop trigger tr_test2_trigger;

트리거가 삭제되었습니다.

 

SQL> drop trigger tr_test2_trigger2;

트리거가 삭제되었습니다.

 

SQL> CREATE OR REPLACE TRIGGER tr_test2_compound_trigger

      FOR INSERT OR UPDATE ON tr_test2

        COMPOUND TRIGGER

      TYPE type_change_tab IS TABLE OF tr_test2_log%ROWTYPE;

      g_change_tab  type_change_tab := type_change_tab();

 

      AFTER EACH ROW IS

      BEGIN

        g_change_tab.extend;

       g_change_tab(g_change_tab.last).id           := tr_test2_log_seq.NEXTVAL;

       IF INSERTING THEN

         g_change_tab(g_change_tab.last).action     := 'INSERT';

       ELSE

         g_change_tab(g_change_tab.last).action     := 'UPDATE';

       END IF;

       g_change_tab(g_change_tab.last).tr_test2_id      := :new.id;

       g_change_tab(g_change_tab.last).cr_date := SYSTIMESTAMP;

     END AFTER EACH ROW;

 

     AFTER STATEMENT IS

     BEGIN

       FOR i IN g_change_tab.first .. g_change_tab.last LOOP

         SELECT COUNT(*)

         INTO   g_change_tab(i).count

         FROM   tr_test2;

       END LOOP;

 

       FORALL i IN g_change_tab.first .. g_change_tab.last

         INSERT INTO tr_test2_log VALUES g_change_tab(i);

        g_change_tab.delete;

     END AFTER STATEMENT;

 

   END tr_test2_compound_trigger;

   /

트리거가 생성되었습니다.

SQL> INSERT INTO tr_test2 (id, name) VALUES (tr_test2_seq.NEXTVAL, 'OJC');

1 개의 행이 만들어졌습니다.


 

, , , , , , , , , , , , , , , , , , , , , , , , ,

Comments