[Oracle] Trigger

트리거

트리거는 DB 안의 특정한 상황이나 동작, 이벤트가 발생할 때 자동으로 실행되는 기능을 정의하는 서브 프로그램입니다.

 

트리거의 장점으로는 먼저 데이터 관련 다양한 작업에 필요한 PL/SQL을 여러개 작성는 수고로움을 줄입니다. 또한, 제약조건만으로 부족한 복잡한 데이터 규칙이 있다면 이를 정의할 수 있습니다. 마지막으로 데이터 변경과 관련된 일련의 정보를 기록할 수 있습니다. 기록하는 점으로 인해, 공유 데이터 관련 보안성, 안전성이 확보가 되고 오류가 발생하더라도 이전 데이터 기록을 통해 대처가 가능합니다.

 

다만 트리거는 주로 타 데이터 추가, 변경 작업이 일어나므로 마구잡이로 사용하면 DB의 성능이 하락할 것입니다. 그래서 용도를 제한할 필요가 있으며, 테이블부터 DB 전체에 대한 DML(INSERT, UPDATE, DELETE), DDL(CREATE, DRP, ALTER) 등 지정할 수 있지만 가장 많이 사용하는 테이블 뷰에 대한 DML 지정 트리거를 사용하는 것이 좋겠습니다.

 

트리거의 선언

 트리거는 아래의 코드와 같이 선언합니다. 생략도 가능하며, 둘 중하나만 고르는 옵션들이 일부 있습니다.

  CREATE OR REPLACE TRIGGER 트리거명
  BEFORE | AFTER
  INSERT | UPDATE | DELETE ON 테이블명
  [REFERENCING OLD AS 이전데이터 사용할 명칭 NEW AS 새로운 데이터 사용할 명칭]  
  [FOR EACH ROW WHEN 조건식]  
  [FOLLOWS 트리거2, 트리거3, ...]  
  [ENABLE | DISABLE]  
  DECLARE  
    선언부  
  BEGIN  
    실행부  
  EXCEPTION  
    예외처리부  
  END [트리거명];

• BEFORE or AFTER : 트리거가 동작할 시점입니다. DML명령어가 동작하기 전이나 동작한 후에 트리거가 동작할 건지 그 시점을 결정합니다.
• INSERT, UPDATE, DELETE ON 테이블명 : 지정한 테이블에 DML이 발생할 때 트리거가 동작하도록 만드는 DML 지정문이며, OR를 중간에 붙여서 한 번에 여럿의 DML문을 지정할 수 있습니다.
• REFERENCING OLD AS 이전 데이터의 별칭
  REFERENCING NEW AS 새로운 데이터의 별칭
  • 이전 데이터는 UPDATE, DELETE 문에서 사용 가능하고 새로운 데이터는 UPDATE, INSERT 문에서 사용 가능합니다. 실행부에서 사용할 때에는 :별칭.컬럼명 으로 사용하게 됩니다.
• FOR EACH ROW WHEN 조건식은 각각의 행에 대해 트리거가 동작할 건지를 결정합니다.
  • WHEN 조건에 해당하는 행에 대해서만 트리거가 동작하게 할 수 있으며, 지정하지 않는 경우 트리거는 딱 한 번만 동작하고 종료합니다.
• 나머지는 생략 가능한 부분으로, FOLLOWS 이하에서 연관 트리거들의 동작 순서를 지정할 수 있고, ENABLE 혹은 DISABLE은 해당 트리거의 활성화 여부를 지정합니다. 따로 지정하지 않으면 ENABLE로 설정합니다.

BEFORE TRIGGER

예를 들어, 급여가 3000 미만으로 입력되면 에러메시지를 출력하는 트리거를 제작합니다.

CREATE OR REPLACE TRIGGER TR_EMP_SAL1
BEFORE
INSERT OR UPDATE OF SAL ON EMP
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
    IF :NEW.SAL < 3000 THEN 
        IF INSERTING THEN
            -- 사용자 정의 에러
            RAISE_APPLICATION_ERROR(-20000, '최저급여보다 낮음');
        ELSIF UPDATING THEN
            RAISE_APPLICATION_ERROR(-20001, '최저급여보다 낮음');
        ELSE RAISE_APPLICATION_ERROR(-20002, '최저급여보다 낮음');
        END IF;
    END IF; --위에서 보면 IF문이 중첩되어있다.
END;
/

이러한 트리거를 만들고, 연봉을 2999 받는 홍길동 데이터를 입력해보겠습니다.

INSERT INTO EMP
VALUES('8001', '홍길동', '취합', '2001', SYSDATE, 2999, 0, '01');

트리거가 발동해서 에러 메시지를 반환함을 알 수 있습니다. 실제로 BEFORE TRIGGER로서 INSERT 함수가 실행되기 전에 트리거가 작동하므로 홍길동 데이터는 생성되지 않았습니다.

 

AFTER TRIGGER라면 어떻게 될 지 보겠습니다.

CREATE OR REPLACE TRIGGER TR_EMP_SAL1
AFTER
--....(동일)
END;
/

수행해봐도 동일하게 출력되고, 따로 추가되지는 않았습니다. 

 

MERGE INTO

조건에 맞는 데이터가 있다면 UPDATE하고 없으면 INSERT하는 쿼리문입니다.

MERGE INTO 테이블명
USING 비교대상이 될 테이블 | 뷰 | 서브쿼리
[OR USING DUAL]
ON (비교조건)
WHEN MATCHED THEN
UPDATE SET
    컬럼명1 = 값1,
    컬럼명2 = 값2,
    ....
| DELETE
WHEN NOT MATCHED THEN 
INSERT (컬럼1, 컬럼2, 컬럼3, ...)
VALUES (값1, 값2, 값3, ...)

DELETE도 할 수 있으며 상당히 유용해보입니다.

 

이번에는 AFTER TRIGGER와 MERGE INTO를 조합해서, 테이블에 데이터가 입력/수정되었을 때, 다른 테이블에 있는 데이터가 입력/수정되어 GRADE도 자동으로 업데이트되는 트리거를 만들어보겠습니다.

 

--T_NCHE_SC 테이블에 데이터가 입력이나 점수가 수정되면
--T_NCHE_SCGR 테이블에 데이터가 자동으로 입력되거나
--점수가 수정된 거는 GRADE가 자동 업데이트되는 트리거
CREATE OR REPLACE TRIGGER TR_NCHE_SCGR
AFTER
INSERT OR UPDATE ON T_NCHE_SC
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
    GRD CHAR(1);
BEGIN
    IF :NEW.RESULT >=90 THEN GRD := 'A';
    ELSIF :NEW.RESULT >= 80 THEN GRD := 'B';
    ELSIF :NEW.RESULT >= 70 THEN GRD := 'C';
    ELSIF :NEW.RESULT >= 60 THEN GRD := 'D';
    ELSE GRD := 'F';
    END IF;
    
    MERGE INTO T_NCHE_SCGR A
    USING DUAL
    ON (A.SNO = :NEW.SNO)
    WHEN MATCHED THEN 
        UPDATE
            SET
                A.RESULT = :NEW.RESULT,
                A.GRADE = GRD
    WHEN NOT MATCHED THEN
        INSERT (A.SNO, A.SNAME, A.RESULT, A.GRADE)
        VALUES (:NEW.SNO, :NEW.SNAME, :NEW.RESULT, GRD);
END;
/

이 트리거는 T_NCHE_SC 테이블에 데이터가 입력이나 점수가 수정되면 T_NCHE_SCGR 테이블에 데이터가 자동으로 입력/수정되면서 GRADE가 자동 업데이트시키는 트리거입니다.

먼저 관우라는 학생은 성적이 84점이고 등급은 B인데, 65점으로 변경해보겠습니다.

UPDATE T_NCHE_SC1
    SET
        RESULT = 65
    WHERE SNO = 915305;

트리거를 통해, 수행 후에 GRADE가 B에서 D로 자동으로 변경된 점을 확인할 수 있습니다.