[SQL] 트리거 (Trigger)

안녕하세요. 이번 글은 트리거에 대한 설명입니다.

- 트리거(Trigger)란?

• 데이터베이스 내 특정 이벤트(상황, 동작)이 발생할 때 자동으로 실행되는 기능을 정의하는 PL/SQL 서브 프로그램
• 특정 데이터 또는 작업과 연관된 여러 작업을 통합하여 한 번에 수행가능
• 복잡한 데이터 규칙을 정의하여 데이터 처리 가능
• 데이터 변경 관련 일련의 정보 기록 가능함으로써 데이터 보안성, 안정성 보장가능하며 문제 시 대처 능력 기대 가능

CREATE OR REPLACE [트리거명]

BEFORE [|| AFTER]
	INSERT || UPDATE || DELETE ON [테이블명]

REFERENCING OLD AS old || NEW AS new

FOR EACH ROW WHEN [조건식]

FOLLOWS [트리거명]

ENABLE || DISABLE

DECLARE
	[선언부]
    
BEGIN
	[실행 로직]
 
EXCEPTION
	[예외 처리부]

END;
/

• BEFORE, AFTER : 트리거가 작동할 타이밍 지정하는 역할 담당 (명령어 실행 전과 후로 분류)
• INSERT, UPDATE, DELETE ON [테이블명] : 트리거가 작동할 DML 명령어 지정하며 여러 개일 경우 || (OR) 사용
• REFERENCING OLD AS OLD, NEW AS NEW : 생략 가능하며 DML로 인해 변경되는 행의 변경 전과 후를 참조하는데 사용
• FOR EACH ROW WHEN [조건식] : 생략 가능하며 DML 명령어에 영향받는 행별로 실행할 지 한 번만 실행할 지를 결정하는 역할 담당
• FOLLOWS [트리거명] : 생략 가능하며 여러 관련된 트리거 실행 순서 지정하는 역할 담당
• ENABLE, DISABLE : 생략 가능하며 트리거의 활성화 여부 체크하는 역할 담당

CREATE OR REPLACE TRIGGER TEST_TRIG_001

BEFORE
    INSERT OR UPDATE ON STUDENT_INFO

    FOR EACH ROW

BEGIN
       IF INSERTING THEN
           INSERT INTO STUDENT_INFO_LOG (STU_NAME, STU_NUM, STU_GRADE, STU_COLLAGE, STU_DEPT, STU_ACA, STU_GENDER, ADD_DATE, CUD_TYPE)
           VALUES (:NEW.STU_NAME, :NEW.STU_NUM, :NEW.STU_GRADE, :NEW.STU_COLLAGE, :NEW.STU_DEPT, :NEW.STU_ACA, :NEW.STU_GENDER, SYSDATE, 'C');
       ELSIF UPDATING THEN
           INSERT INTO STUDENT_INFO_LOG (STU_NAME, STU_NUM, STU_GRADE, STU_COLLAGE, STU_DEPT, STU_ACA, STU_GENDER, ADD_DATE, CUD_TYPE)
           VALUES (:NEW.STU_NAME, :NEW.STU_NUM, :NEW.STU_GRADE, :NEW.STU_COLLAGE, :NEW.STU_DEPT, :NEW.STU_ACA, :NEW.STU_GENDER, SYSDATE, 'U');
       ELSE
           RAISE_APPLICATION_ERROR(-20003, 'ETC ERROR');
       END IF;
END;
/

상단 쿼리문은 학생정보 테이블에 대한 CRUD 작업 시 학번에 대한 정보를 처리했던 이력을 관리하기 위한 트리거이다. 이때 작동할 타이밍은 BEFORE로 설정하였으며 명령어 실행 전 해당 트리거가 작동되며 작동 후 DML 명령어에 대한 실행 결과가 발생하는 점을 확인할 수 있다.

SELECT * FROM USER_TRIGGERS;

상단 USER_TRIGGERS 데이터 사전을 사용하면 본인이 만들었던 트리거를 확인할 수 있다.

INSERT INTO STUDENT_INFO 
VALUES ('김땡땡', '20226459', '3', '간호대학', '간호학과', '재학', '남자');

UPDATE STUDENT_INFO
   SET STU_ACA = '휴학'
 WHERE STU_NUM = '20226459';

상단처럼 INSERT와 UPDATE문을 실행하면 트리거가 작동할 것이다.

실행 후 트리거에서 INSERT 대상이었던 STUDENT_INFO_LOG 테이블을 조회하면 다음과 같이 이력이 저장되어있는 점을 확인할 수 있다.

CREATE OR REPLACE TRIGGER TEST_TRIG_002

AFTER
    DELETE ON STUDENT_INFO

    FOR EACH ROW

BEGIN
       IF DELETING THEN
           INSERT INTO STUDENT_INFO_LOG (STU_NAME, STU_NUM, STU_GRADE, STU_COLLAGE, STU_DEPT, STU_ACA, STU_GENDER, ADD_DATE, CUD_TYPE)
           VALUES (:OLD.STU_NAME, :OLD.STU_NUM, :OLD.STU_GRADE, :OLD.STU_COLLAGE, :OLD.STU_DEPT, :OLD.STU_ACA, :OLD.STU_GENDER, SYSDATE, 'D');
       ELSE
           RAISE_APPLICATION_ERROR(-20003, 'ETC ERROR');
       END IF;
END;
/

다음은 삭제할 때 작동하는 트리거를 실행할 것인데, 이때 이전과는 다르게 AFTER와 OLD 키워드를 사용하였다. 이를 통해 상단의 트리거가 DML문이 실행된 후에 트리거가 작동하는 것과 동시에 OLD 키워드를 통해 갱신 또는 삭제 이전의 데이터를 의미한다는 점을 알 수 있다.

DELETE FROM STUDENT_INFO WHERE STU_NUM = '20226459';

다음과 같이 상단의 쿼리문을 실행하면 이미지처럼 트리거가 실행되면서 삭제한 이력이 테이블에 저장된 점을 확인할 수 있으며 효율적인 이력관리 및 손쉬운 복구가 가능한 점을 알 수 있다.