[오라클] TRIGGER 구문 정리

트리거 구문 정리해 보려 한다. 잘 알아두면 유용하게 쓰이는 오라클 구문이기에 이렇게 정리하는 것이 큰 도움이 될 것 같다.

오라클 JOB을 걸면 일정 주기마다 DML(INSERT,UPDATE,DELETE) 작업을 수행하게 하여 TABLE에 데이터 작업을 해줄 수 있다. 일정 주기나 시간이 아니라, TABLE에 어떤 이벤트가 발생했을 때 알아서 오라클에서 내가 정의 해놓은 작업들을 수행해 준다면 얼마나 좋을까? .... 바로 이때 사용 할 수있는 오라클 구문에는 트리거(TRIGGER)가 있다. 

 

1. 트리거(TRIGGER) 정의

 

- 테이블에 어떤 이벤트가 발생했을 때, 자동으로 사용자가 정의한 PL/SQL 명령을 실행 할수 있는 구문이다. 

 

 

2. 트리거(TRIGGER)  생성 구문

 

CREATE [OR REPLACE] TRIGGER 트리거명 [BEFORE | AFTER]
triggering-event[Insert,delete,update] ON 테이블명
[Referencing OLD AS {변경전 값을 참조하는 변수명} NEW AS {변경 후 값을 참조하는 변수명}]
[FOR EACH ROW]

[WHEN (condition)]?
DECLARE
--변수선언
BEGIN
--트리거 PL/SQL 명령 작성
EXCEPTION
END;

 

#구문에 대한 상세 설명#

CREATE [OR REPLACE] TRIGGER [schema.]trigger : 트리거 생성, 재생성 명령이다?

BEFORE : INSERT, UPDATE, DELETE문이 실행되기 전에 트리거가 실행 된다.

AFTER : INSERT, UPDATE, DELETE문이 실행된 후 트리거가 실행 된다.

trigger_event : INSERT, UPDATE, DELETE 중에서 한 개 이상 올 수 있다.

FOR EACH ROW : 이 옵션이 있으면 행 트리거가 된다.

 - 행수준 트리거(Row -Level Triggers) : 트랜잭션내의 각 행에 대해 한 번만 수행(컬럼의 각각의 행의 데이터 행 변화가 생길 때마다

                                                       실행되며,각 데이터 해의 값을 제어할 수 있다.)
 - 문장수준 트리거(Statement-level Triggers) : 트랜잭션내에서 한번만 수행되며, 컬럼의 각 데이터 행을 제어 할 수 없다.

[WHEN (condition)]] : 데이터의 처리유형 이외의 조건을 삽입할 경우에 사용???????????

※ 따라서, 위의 옵션을 어떻게 사용하느냐에 따라 만들수 있는 트리거 유형은

트리거 이벤트(INSERT,UPDATE,DELETE) 3종류, FOR EACH ROW 유무에 따른 2종류, Before와 After에 대해 2종류로

3*2*2 = 12가지 유형을 만들 수 있다.

3. 트리거(TRIGGER)  생성시 고려사항

 

1. 트리거는 각 테이블에 최대 3개까지 가능하다

2. 트리거 내에서는 COMMIT,ROLLBACK 문을 사용할 수 없다.

3. 이미 트리거가 정의된 작업에 대해 다른 트리거를 정의하면 기존의 것을 대체한다.

4. 뷰나 임시 테이블을 참조할 수 있으나 생성 할 수는 없다.

5. 트리거 동작은 이를 삭제하기 전까지 계속된다.

 

4. 트리거(TRIGGER)   활성 / 비활성화

 

ALTER TRIGGER [schema.]trigger DISABLE;   ==> 비활성화

ALTER TRIGGER [schema.]trigger ENABLE; ==> 활성화

 

5. 트리거(TRIGGER)  상태  확인

 

select table_name, status from user_triggers; => 테이블 명과, 트리거 활성 상태를 확인 할 수 있다.

 

6. 트리거(TRIGGER)  삭제 구문

 

DROP TRIGGER [schema.]trigger

 

7. 트리거(TRIGGER)  작성 예문

 

예문 1) 테이블 변경 이력 관리를 위한 TRIGGER 구문

 

?CREATE OR REPLACE TRIGGER NAMDM.RECORD_CHANGE_LOG_TO_MDMTB090
AFTER DELETE OR INSERT OR UPDATE
OF VALUE
ON NAMDM.MDMTB010
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  -- your code here
  -- (Trigger template "Default" could not be loaded.)  
 
  IF :NEW.VALUE != :OLD.VALUE THEN   -- 값이 변경된 경우만 저장!!!
    INSERT INTO mdmtb090
    (
      SEQ, ITEM_ID, ATTR_ID, SCOPE,
      OLD, NEW, BIGO, CHG_EMP, DAT 
    )
    VALUES
    (
      MDM_TRIGGER_SEQ.NEXTVAL, NVL(:NEW.ITEM_ID,:OLD.ITEM_ID), NVL(:NEW.ATTR_ID,:OLD.ATTR_ID), NVL(:NEW.SCOPE,:OLD.SCOPE),
      :OLD.VALUE, :NEW.VALUE, '', :NEW.CHG_EMP, SYSDATE
    );
  END IF;
END;

 

예문 2) 테이블에 이벤트 발생시 다른 테이블에 MERGE 하기 위한 TRIGGER 구문

 

CREATE OR REPLACE TRIGGER NATEST.CALL_ACT_CONFIRM
AFTER DELETE OR INSERT OR UPDATE
OF IF_FLAG
ON NATEST.TEST_IF
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
   
     IF :NEW.IF_FLAG = 'S' AND (:NEW.REVISION_ID <> '000' OR :NEW.REVISION_ID <> 'MRP' OR  :NEW.REVISION_ID <> 'EXT') THEN
        --SP_TESTTB020_PROCESS('ACT', 'CONFIRM',:NEW.M00001,:NEW.REVISION_ID,:NEW.M00069,:NEW.ASSINGEE_ID);
          /* 4-2-1. TESTTB010로 데이터 복사 */
        MERGE INTO TESTTB010 ORG
        USING (
                    SELECT    T1.ITEM_ID,
                            T1.ATTR_ID,
                            T1.SCOPE,
                            T1.VALUE    WORK_VALUE,
                            T1.CHG_EMP,
                            T1.CHG_DATE
                    FROM    TESTTB011    T1,
                            TESTTB020    T2
                    WHERE    T1.ITEM_ID    = T2.ITEM_ID
                    AND        T1.REV_NO    = T2.REV_NO
                    AND        T2.STATUS    = 'A'
                    AND        T1.ITEM_ID    = :NEW.M00001
                    AND        T1.REV_NO    = :NEW.REVISION_ID    
                    AND        T2.PLANT    = :NEW.M00069     
                    AND        (T1.SCOPE = 'G' OR T1.SCOPE = 'C' OR T1.SCOPE = T2.REGION OR T1.SCOPE = T2.COMPANY OR T1.SCOPE = T2.PLANT)
              ) TMP
          ON  (
                    ORG.ITEM_ID    = TMP.ITEM_ID
              --AND    ORG.REV_NO     = TMP.REV_NO
              AND    ORG.ATTR_ID    = TMP.ATTR_ID
              AND    ORG.SCOPE    = TMP.SCOPE
              ) WHEN MATCHED THEN
                     UPDATE
                        SET    ORG.VALUE        = TMP.WORK_VALUE,
                            ORG.CHG_EMP        = TMP.CHG_EMP,
                            ORG.CHG_DATE    = TMP.CHG_DATE
                WHEN NOT MATCHED THEN
                INSERT
                (
                  ORG.ITEM_ID, ORG.ATTR_ID, ORG.SCOPE, ORG.VALUE, ORG.CHG_EMP, ORG.CHG_DATE
                ) VALUES
                (
                  TMP.ITEM_ID, TMP.ATTR_ID, TMP.SCOPE, TMP.WORK_VALUE, TMP.CHG_EMP, TMP.CHG_DATE
                );
               
        /* 4-2-2. ACT 데이터 'Y' */
        MERGE INTO TESTTB020 ORG
        USING (
                SELECT    TO_CHAR(SYSDATE,'YYYYMMDD') PROC_DATE,
                        ITEM_ID,
                        REV_NO,
                        'A'            STATUS,
                        REGION,
                        MATR_TYPE,
                        FUNC_ROLE,
                        COMPANY,
                        PLANT,
                        'Y'            CONFIRM,
                        :NEW.ASSINGEE_ID   CHG_EMP
                FROM     TESTTB020
                WHERE    ITEM_ID    = :NEW.M00001
                AND        REV_NO = :NEW.REVISION_ID    
                AND        PLANT    = :NEW.M00069    
                AND        STATUS    = 'C'
               
              ) TMP
          ON  (
                    ORG.ITEM_ID    = TMP.ITEM_ID
              AND    ORG.REV_NO     = TMP.REV_NO
              AND    ORG.STATUS    = TMP.STATUS
              AND    ORG.REGION    = TMP.REGION
              AND    ORG.MATR_TYPE    = TMP.MATR_TYPE
              AND    ORG.COMPANY        = TMP.COMPANY
              AND    ORG.PLANT        = TMP.PLANT
              ) WHEN MATCHED THEN
                     UPDATE
                        SET    ORG.CONFIRM        = TMP.CONFIRM,
                               ORG.EMP_NO         = TMP.CHG_EMP,
                               ORG.CHG_DATE       = SYSDATE
                WHEN NOT MATCHED THEN
                INSERT
                (
                  ORG.PROC_DATE, ORG.ITEM_ID, ORG.REV_NO, ORG.STATUS, ORG.REGION, ORG.MATR_TYPE, ORG.COMPANY, ORG.PLANT, ORG.CONFIRM, ORG.EMP_NO, ORG.CHG_DATE
                ) VALUES
                (
                    TMP.PROC_DATE, TMP.ITEM_ID, TMP.REV_NO, TMP.STATUS, TMP.REGION, TMP.MATR_TYPE, TMP.COMPANY, TMP.PLANT, TMP.CONFIRM, TMP.CHG_EMP, SYSDATE
                );
     END IF;
     IF :NEW.IF_FLAG = 'S' AND (:NEW.REVISION_ID = '000' OR :NEW.REVISION_ID = 'MRP' OR  :NEW.REVISION_ID = 'EXT') THEN  
        MERGE INTO TESTTB010 ORG
        USING (
                SELECT T1.ITEM_ID,
                        T1.ATTR_ID,
                        T1.SCOPE,
                        T1.VALUE WORK_VALUE,
                        T1.CHG_EMP,
                        T1.CHG_DATE
                FROM TESTTB011 T1,
                        MDTAT010 T4
                WHERE T1.ITEM_ID = :NEW.M00001
                AND     T1.REV_NO = :NEW.REVISION_ID
                AND  T1.ATTR_ID = T4.ID
                AND  (T1.SCOPE = 'G' OR T1.SCOPE = 'C' OR T1.SCOPE = :NEW.REGION OR T1.SCOPE||'C' = (SELECT DISTINCT COMPANY FROM MDTCT050 WHERE PLANT = :NEW.M00069)||T4.CONTROL_LEVEL OR T1.SCOPE||'P' = :NEW.M00069||T4.CONTROL_LEVEL)     
              ) TMP
          ON  (
                    ORG.ITEM_ID = TMP.ITEM_ID
              --AND ORG.REV_NO  = TMP.REV_NO
              AND ORG.ATTR_ID = TMP.ATTR_ID
              AND ORG.SCOPE = TMP.SCOPE
              ) WHEN MATCHED THEN
                     UPDATE
                        SET ORG.VALUE  = TMP.WORK_VALUE,
                                ORG.CHG_EMP  = TMP.CHG_EMP,
                                ORG.CHG_DATE         = TMP.CHG_DATE
                WHEN NOT MATCHED THEN
                INSERT
                (
                  ORG.ITEM_ID, ORG.ATTR_ID, ORG.SCOPE, ORG.VALUE, ORG.CHG_EMP, ORG.CHG_DATE
                ) VALUES
                (
                  TMP.ITEM_ID, TMP.ATTR_ID, TMP.SCOPE, TMP.WORK_VALUE, TMP.CHG_EMP, TMP.CHG_DATE
                ); 
     END IF;
                
    END;