AFTER COMMIT TRIGGER

ORACLE

커밋 이후 트리거

 

AFTER COMMIT TRIGGER는

말그대로 commit이후에 작동하는 트리거를 말하는데

구글에서 검색해서 조사해보면

전문가들은 대부분

"트리거를 그런식으로 사용하지 말아라 "

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:363719300346898052

-asktom

https://stackoverflow.com/questions/4098771/how-to-define-a-trigger-on-commit-in-oracle

-stackoverflow

라고 답변한다.

 

하지만, 정말로 어쩔 수 없을때는 사용해야하니, 일부 개발자,DBA들은 비공식적인,

일종의 트릭을 쓰는 방식으로

AFTER COMMIT TRIGGER를 구현 해왔다.

참고로 공인된 방법이 아니기 때문에

위험 부담이 크다.

꼭 어쩔 수 없는 경우에 철저히 조사하여 적용하자.

 

AFTER COMMIT TRIGGER를 구현하는 방법은 바로 

job을 이용하는 것이다.

정확히 말하자면, 여기에서 설명할 것은

DBMS_JOB을 이용하는 방법이다.

DBMS_SCHEDULER가 아니다.

 

(

DBMS_SCHEDULER은 10G 이후에 지원되는 기능으로써, DBMS_JOB보다 발전된 기능을 보유하고있다. 허나 암묵적으로 COMMIT를 수행하기 때문에 이번 예시하고는 맞지않다.

)

 

방법을 설명하자면 먼저

프로시저를 만든다.

프로시저에는 COMMIT이후 원하는 동작을 수행할 명령어를 넣는다.

 

그다음 트리거를 만든다.

트리거 안에는 DBMS_JOB을 등록하는 명령어를 넣고,

DBMS_JOB에는 프로시저를 실행하게끔 만든다.

 

그러면 , DELETE,UPDATE,INSERT 등으로

DML이 발생해도 DBMS_JOB은 COMMIT이후에 등록되기 때문에

원하는 동작은 COMMIT이후에 동작하게된다.

 

예를 들어 본인이 SCOTT의 EMP 테이블에 ROW가 DROP되면, 그와 똑같은 ENAME을 가진 ORACLE USER를 DROP 시키는 TRIGGER를 만든다고 가정한다.

 

그러면 먼저, USER를 DROP 시키는 프로시저를 생성한다.

 

CREATE OR REPLACE PROCEDURE PRC_DROP_ENAME(V_ENAME IN VARCHAR2)

AS

V_USER VARCHAR2(4000) := '';

BEGIN

    --DBA_USERS에서 사용자 목록을 조회

    SELECT USERNAME INTO V_USER

    FROM DBA_USERS

    WHERE USERNAME = V_ENAME;

                   

    IF V_USER IS NOT NULL THEN

          EXECUTE IMMEDIATE 'DROP USER '||V_USER||' CASCADE';

    END IF;

END ;

/

 

 

그런다음 DBMS_JOB을 등록하는 트리거를 생성한다.

 

CREATE OR REPLACE TRIGGER TRG_DELETE_ENAME

AFTER DELETE ON SCOTT.EMP

FOR EACH ROW

DECLARE

 JOBNO NUMBER;

BEGIN

    --USER CHECK

    DBMS_JOB.SUBMIT -- 잡 등록

      (JOB  => JOBNO -- 잡 생성

      ,WHAT => 'PRC_DROP_ENAME('''||:OLD.ENAME||''');' -- 실행할 프로시저

      ,NEXT_DATE => SYSDATE-- JOB 실행 시간

      );

END ;

/

 

그러면 이제 EMP TABLE에 DELETE 를 걸더라도 바로 DROP 되는것이 아닌 COMMIT이후에 DROP이 실행된다.

 

물론 ROLLBACK을 할 경우 USERDROP,DELETE 둘다 실행되지 않는다.