Trigger(트리거)

트리거는 다른 저장 서브프로그램들처럼 고유한 이름을 가지고, 데이터베이스에 저장되며 반복적으로 호출되는 서브프로그램이다. 하지만 다른 저장 서브프로그램과는 달리 트리거는 프로그램에서 직접 호출되어 실행될 수 없다. 트리거는 데이터베이스에 특정 이벤트가 발생할 때마다 데이터베이스에 의해 자동으로 실행된다.

CREATE OR REPLACE TRIGGER tr_emp_ins_upd_validate
	BEFORE INSERT OR UPDATE OF sal, comm ON emp
    REFERENCING OLD AS OLD NEW OR NEW
    FOR EACH ROW
BEGIN
 IF INSERTING THEN
 	IF :NEW.sal < 0 THEN
    	DBMS_OUTPUT.PUT_LINE('급여가 음수입니다.');
        :NEW.sal := NULL;
    END IF;
    IF :NEW.comm < 0 THEN
     DBMS_OUTPUT.PUT_LINE('커미션이 음수입니다.');
     	:NEW.comm := NULL;
    END IF;
 ELSIF UPDATING THEN
 	IF :OLD.sal > :NEW.sal THEN
    	DBMS_OUTPUT.PUT_LINE('급여가 낮아졌습니다.');
        :NEW.sal := :OLD.sal;
    END IF;
    IF :OLD.comm > :NEW.comm THEN
    	DBMS_OUTPUT.PUT_LINE('급여가 낮아졌습니다.');
        :NEW.sal := :OLD.sal;
    END IF;
    IF :OLD.comm > :NEW.comm THEN
    	DBMS_OUTPUT.PUT_LINE('커미션이 낮아졌습니다.');
        :NEW.comm := :OLD.comm;
    END IF;
 END IF;
END;

트리거는 다양한 용도로 사용된다.

• 한 테이블에 변경이 발생하면 동시에 다른 테이블들에 변경을 가한다.
• 데이터의 변경이 데이터베이스에 저장되기 전에 값을 검증하고 필요시 다른 값으로 변경한다.
• 조건 판단에 따라서 이벤트의 발생을 허용하거나 차단한다,
• 발생한 이벤트에 대한 로그를 남긴다.
• 자동으로 계산 칼럼의 값을 생성한다.
• 뷰에 대한 DML문을 수행 시 다른 테이블을 변경한다.(INSTEAD OF 트리거)
• 제약 조건으로 생성할 수 없는 복잡한 업무 규칙을 적용하여 데이터의 무결성을 보장한다.
• 분산 데이터베이스 환경에서 부모 테이블과 자식 테이블 간의 참조 무결성을 보장한다.

트리거의 종류

• DML 트리거: 테이블이나 뷰에 생성되는 트리거로 INSERT, UPDATE, DELETE문과 같은 DML 이벤트에 의해 실행된다. (MERGE문에 대한 트리거는 따로 없고 INSERT와 UPDATE 트리거가 사용된다.) 테이블이나 뷰에 DML이 수행되기 전이나 후에 추가적인 동작을 하거나 조건 검사를 통해 원치 않는 변경 수행시 변경을 차단하는 목적으로 사용됨
• DDL 트리거: 데이터베이스나 특정 스키마에 DDL이 수행되는 경우에 실행된다. DDL이 수행되기 전이나 후에 추가적인 동작을 하거나 DDL에 대한 감사 목적의 로깅을 하거나 조건 검사를 통해 원치 않는 DDL 수행 시 이를 차단하는 목적으로 사용된다,
• 데이터베이스 트리거: 데이터베이스의 시작(Startup)이나 종료(Shutdown), 특정 에러의 발생, 로그린, 로그아웃과 같은 이벤트 발생 시 실행된다. 데이터베이스의 상태가 변하기 전이나 후에 추가적인 동작(특정 DB 파라미터 지정, DB상태 로깅, 로그인 이력 로깅 등)을 수행하기 위해 사용된다.

일반적으로 데이터베이스 개발자들이 사용하는 트리거는 DML트리거이다. DDL트리거나 데이터베이스 트리거는 주로 DBA나 시스템 관리자가 사용하기에 적합한 트리거로, 일반 개발자들이 사용할 일은 없을 것이다.

DML 트리거의 구조

CREATE OR REPLACE TRIGGER 트리거명
발생시점  DML이벤트 [ OR 칼럼명 ] ON 테이블 또는 뷰
참조절
FOR EACH ROW
WHEN (조건)
DECLARE
	선언부
BEGIN
	실행부
EXCEPTION
	예외처리부
END;

모든 DML트리거는 발생시점과 트리거를 유발하는 DML이벤트를 속성으로 가진다. 따라서 2번줄은 필수적으로 지정해야 한다. 3 ~ 7번 줄은 옵션으로 필요시에만 지정한다.

 

2번 줄의 발생시점은 트리거가 언제 실행될 것인지를 지정하는 데, 다음의 세가지 값 중 하나를 가질 수 있다.

• BEFORE: 트리거의 실행을 유발한 이벤트가 수행되기 전에 트리거를 실행
• AFTER: 트리거의 실행을 유발한 이벤트를 수행한 후에 트리거를 실행
• INSTEAD OF: 뷰에 대해 DML을 실행할 경우 DML 대신에 트리거를 실행 

2번 줄의 DML이벤트는 트리거를 실행하는 DML의 종류를 지정한다.

• INSERT: INSERT문이 실행될 때 트리거를 실행
• UPDATE: UPDATE문이 실행될 때 트리거를 실행
• DELETE: DELETE문이 실행될 때 트리거를 실행

DML이벤트는 INSERT OR UPDATE OR DELETE와 같이 OR을 사용하여 이벤트를 복합 지정할 수 있다. 이벤트의 발생 시점과 DML 이벤트의 종류는 서로 결합하여 사용할 수 있다.

 

ON뒤에 따라오는 테이블 또는 뷰는 DML 대상 테이블 또는 뷰의 이름을 지정한다. 발생시점이 INSTEAD OF일 경우에는 뷰만 지정할 수 있다. 옵션으로 OF절을 사용하여 이벤트가 발생하는 칼럼 목록을 지정할 수 있다.

 

INSERT ON emp
UPDATE ON emp
DELETE ON emp
UPDATE OF sal, comm ON emp
INSERT OR DELETE OR UPDATE OF sal, comm ON emp

DML 이벤트 다음에 ON절과 OF절을 지정할 수 있는데, ON 다음에는 테이블이나 뷰명이 하나만 올 수 있고, OF 다음에는 하나 이상의 칼럼명이 올 수 있다. 

 

참조절은 로우 레벨에서 발생하는 DML 이벤트의 변경 전과 후의 값을 가지는 레코드의 이름을 지정한다. 참조절을 생략할 경우에는 변경 전의 값을 가지는 레코드명은 OLD고, 변경 후의 값을 가지는 레코드명은 NEW다. 참조절은 이 이름을 다음과 같이 변경할 수 있도록 한다. 특별한 이유가 없다면 이름을 변경하지 않는 것이 좋다.

REFERENCING OLD AS before_rec
REFERENCING NEW AS after_rec
REFERENCING OLD AS before_rec NEW AS after_rec

DML에 따라서 OLD레코드와 NEW 레코드의 필드는 값을 가질 수도 있고, 값을 가지지 않을 수도 있다.

DML 이벤트	OLD.필드 값	NEW.필드 값
INSERT	NULL	INSERT 결과값
UPDATE	UPDATE 전 값	UPDATE 결과값
DELETE	DELETE 전 값	NULL

INSERT 트리거에서는 OLD레코드가 존재하지 않으며, DELETE 트리거에서는 NEW 레코드가 존재하지 않는다. DML 이벤트와 시점에 따라서 OLD레코드와 NEW 레코드의 변경에는 다음과 같은 제한이 있다.

• 모든 DML 트리거에서 OLD 레코드의 필드 값은 변경할 수 없다.
• INSERT 트리거에서는 OLD 레코드의 필드 값이 존재하지 않는다.
• DELETE 트리거에서는 NEW 레코드의 필드 값이 존재하지 않는다.
• AFTER 트리거에서는 NEW 레코드의 필드 값을 변경할 수 없다.

BEFORE 트리거에서 NEW 레코드의 필드 값을 변경하면 변경된 값이 DML의 결과로 데이터베이스에 저장된다.

FOR EACH ROW는 트리거가 로우 단위로 실행되도록 명시한다. FOR EACH ROW의 명시 여부에 따라 트리거는 로우 단위 트리거와 문장 단위 트리거로 분류된다.

 

문장 단위 트리거에서는 DML의 결과로 여러 로우가 변경되더라도 하나의 SQL문에 대해 한 번의 트리거만이 실행되나, 로우 단위 트리거에서는 변경되는 매 로우마다 트리거가 실행된다는 점은 반드시 기억해야 한다. 

문장 단위 트리거에서는 OLD와 NEW 레코드를 사용할 수 없다.

 

5번 줄의 WHEN은 FOR EACH ROW가 지정된 경우에만 사용할 수 있는데, WHEN 뒤에 지정되는 조건에 따라 트리거 실행부가 실행될지 말지를 결정한다. WHEN 조건은 대부분 OLD나 NEW 레코드를 사용한다. 

 

참조절에서 정의한 레코드 OLD와 NEW의 사용시 주의한 점 하나는 WHEN절의 조건에서는 'OLD.sal < 0'처럼 레코드에 호스트 변수임을 나타내는 ':'을 붙이지 않지만 트리거 본체(선언부,실행부. 예외처리부)에서는 ':NEW.sal := :OLD.sal'처럼 ':'을 붙여야 한다는 것이다.

 

트리거의 호출 순서

트리거의 호출 순서는 트리거의 시점과 실행 단위에 따라 다음 순서에 따른다.

1. BEFORE 시점의 문장 단위 트리거
2. BEFORE 시점의 로우 단위 트리거
3. AFTER 시점의 로우 단위 트리거
4. AFTER 시점의 문장 단위 트리거

동일한 시점과 실행 단위를 가진 경우라면 트리거의 실행 순서를 지정해야 한다. 

트리거의 실행 순서는 FOLLOWS 키워드를 사용하여 명시한다. FOLLOWS는 자신보다 먼저 실행될 트리거를 지정한다.

-- 트리거 tr_a 다음에 tr_b가 호출되는 프로그램
CREATE OR REPLACE TRIGGER tr_a
BEFORE UPDATE ON emp FOR EACH ROW
BEGIN
 ...
END;
/

CREATE OR REPLACE TRIGGER tr_b
BEFORE UPDATE ON emp FOR EACH ROW FOLLOWS tr_a
BEGIN
 ...
END;
/

동일한 시점과 실행 단위를 가지는 트리거를 여러 개 만드는 것은 좋은 방법이 아니다. 하나의 트리거로 합치는 것이 가장 좋은 방법이다.

트리거의 제약 사항

• 크기: 트리거의 소스 코드 크기는 32KB를 넘지 못한다. 만약 그 이상 커진다면 이를 다른 저장 서브프로그램으로 만들고 트리거에서 저장 서브프로그램을 호출하는 방식으로 구현하는 것이 좋다.
• LONG 타입 변수 사용: 트리거에서는 LONG 타입 또는 LONG RAW 타입의 변수를 선언할 수 없다.
• 변경 읽기: 트리거는 자신을 실행하도록 한 DML이 변경한 테이블을 조회하거나 변경할 수 없다. 이를 시도하면 ORA-04091 오류가 발생하며 트리거를 유발한 SQL문은 롤백된다.
• DDL, TCL의 실행: 트리거 내에서는 COMMIT이나 ROLLBACK 같은 트랜잭션 제어가 불가능하다. 트랜잭션 제어를 하는 DDL도 묵시적으로 실행 불가능하다. 트리거에서 DDL이나 TCL을 실행하려면 자치 트랜잭션을 사용해야 한다. 자치 트랜잭션을 사용하려면 트리거의 DECLARE절에 PRAGMA AUTONOMOUS_TRANSACTION을 선언해야 한다.
• 서브프로그램 호출: 트리거는 트랜잭션 제어를 하는 서브프로그램을 선언할 수 없다.