Trigger

트리거란 이름이 있는 데이터베이스 오브젝트로서 데이터베이스가 미리 정해 놓은 조건을 만족하거나 어떤 동작이 수행되면 자동적으로 수행되는 저장 프로시저이다. 또한 테이블과 연관 되어 있으며, 특정 이벤트가 테이블에 대해 발생하면 동작을 하게 된다. 예를 들면, 아래의 명령문은 하나의 테이블과 하나의 INSERT 트리거를 생성한다.

트리거(Trigger)의 원 의미는 ‘방아쇠’이다. 데이터베이스에서 트리거는 바로 이러한 의미를 유추한다면 어떤 역할을 하는지 쉽게 짐작할 수 있을 것이다

데이터베이스에서 트리거는 특정 이벤트(event)나 DDL, DML 문장이 실행되었을 때, 자동적으로 어떤 동작(Operation)이나 처리를 수행하도록 하는 데이터베이스 객체의 하나이다. 일반적으로 트리거는 임의의 테이블에 데이터를 추가(insert), 삭제(delete), 갱신(delete) 할때, 또 다른 테이블에 있는 데이터를 자동적으로 조작할 경우에 사용된다. 이러한 경우 가장 흔히 사용되는 형태가 로그(log) 정보를 기록하는 경우이다.

예를 들어보자.
신규 사원을 등록하려면 employees 테이블에 데이터를 한 건 입력해야 한다. 그런데, 신규사원을 등록(입사처리)하거나 삭제(퇴사처리)할 경우, 누가, 언제 작업 했는지를 기록해야한다고 하자. 이러한 경우 일반적으로 로그 테이블을 별도로 생성하여 그 정보를 기록해 두는데, 입사처리를 수행한 뒤 로그 테이블에 또 다시 정보를 기록하는 일은 단순한 작업이면서도 번거로운 작업이다. 따라서 로그 테이블에 정보를 저장하는 작업을 자동화 하는 것이 여러 모로 좋은 방법이라 할 수 있다. 물론 이러한 로그 정보처리는 어플리케이션에서 처리할 수도 있지만, 데이터베이스 상에서 직접 처리한다면 훨씬 더 효율적일 것이다. (마치 저장 프로시져를 사용하거나 테이블의 컬럼에 디폴트 속성을 넣는 것처럼) 바로 이러한 경우에 트리거를 사용하여 처리하게 된다.

트리거의 사용
간단한 예를 들어, 트리거에 대해 좀 더 자세히 살펴보도록 하자.
먼저 employees 테이블에 있는 정보에 변경사항이 가해질 경우 이 내용을 저장하는 로그 테이블을 만들어 보자.

CREATE TABLE emp_logs (
employee_id NUMBER, /* 사번 */
user_name VARCHAR2(30), /* 작업자 */
proc_type VARCHAR2(30), /* 작업 유형 (insert, update, delete) */
proc_date DATE DEFAULT SYSDATE); /* 작업일자 */

이제 employees 테이블에 데이터를 신규 입력할 경우 emp_logs 테이블에 해당 정보를 저장하는 트리거를 만들어 보자.

CREATE TRIGGER emp_logs_tr
BEFORE INSERT ON employees
FOR EACH ROW
WHEN (new.employee_id > 0)
DECLARE
v_user varchar2(10);
BEGIN
SELECT USER
INTO v_user
FROM DUAL;
INSERT INTO emp_logs (employee_id, user_name, proc_type)
VALUES ( :new.employee_id, v_user, 'INSERT');
END;

트리거는 특정한 처리를 하는 데이터베이스 객체이므로, 저장 프로시져와 그 형태가 비슷하다 할 수 있다. emp_logs_tr 이란 이름의 트리거는 employees 테이블에 데이터를 insert할 경우 작동해서, 신규로 입력되는 사원번호(:new.employee_id), 작업자(v_user), 작업유형(‘INSERT’) 정보를 emp_logs 테이블에 저장하고 있다. 트리거 생성 구문을 자세히 알아보자.

• CREATE OR REPLACE : 뷰나 프로시져 생성 처럼 이 구문을 사용하여 트리거 내용을 수시로 변경할 수 있다. 즉 DROP 하고 다시 생성할 필요가 없다.
• BEFORE : 트리거를 언제 실행시킬지를 명시한다. BEFORE나 AFTER를 명시할 수 있는데, 위 트리거의 경우 employees 테이블에 신규 데이터를 넣기 전에 실행된다. 즉 신규 데이터를 데이터 파일에 쓰기 전에 수행된다.
• INSERT ON : INSERT 문이 실행되었을 경우 트리거를 수행하라는 의미이다. 만약 INSERT와 UPDATE 모두에 해당될 경우에는 ‘INSERT OR UPDATE ON’ 처럼 OR 를 사용하여 명시할 수 있다.
• FOR EACH ROW : employees 테이블에서 변경이 되는 각각의 로우에 대해 트리거가 수행됨을 의미한다. 예를 들어, INSERT도 마찬가지지만 UPDATE나 DELETE의 경우 한 번의 SQL문 수행에 의해 여러 개의 로우가 변경될 수 있다. 이러한 경우 변경이 가해진 로우 각각에 대해 트리거가 수행됨을 의미한다. 즉 10개의 로우가 변경되면 트리거가 10번이 수행되게 된다. 이러한 유형의 트리거를 로우 트리거라고 하며, 변경된 로우의 수에 상관없이한 번만 수행되는 유형의 트리거를 STATEMENT 트리거라 한다.
• WHEN : INSERT나 UPDATE, DELETE가 수행되었을 때, 무조건 트리거는 실행되지만 특정 조건을 만족할 때만 수행하도록 할 수 있는데, 이러한 경우 WHEN 다음에 조건을 명시한다.

• NEW : 트리거에서만 사용할 수 있는 구문으로 new.employee_id 가 의미하는 것은 새로 입력되는 employee_id 값을 말한다. New와 반대 개념으로 old를 사용할 수 있는데, 이는 변경되기 전의 값을 가리킨다.

 

그럼 실제로 데이터를 입력하여 트리거가 작동되는지 살펴보자.

INSERT INTO employees ( employee_id, first_name, last_name, email,
hire_date, job_id, salary, department_id)
VALUES ( 300, '세종', '대왕', 'king', sysdate, 'AD_VP', 2000, 10);
1 rows inserted.

테이블 확인

SELECT employee_id, first_name, last_name
FROM employees
WHERE employee_id = 300;

EMPLOYEE_ID               FIRST_NAME             LAST_NAME
----------------- ----------------- -------------------------
300                               세종                             대왕
1 rows selected
로그 테이블도 확인해 보자.

 SELECT *FROM emp_logs;

EMPLOYEE_ID USER_NAME PROC_TYPE        PROC_DATE
------------ ----------- ------------- -------------------
300                    HR               INSERT           2008-01-21 13:38:37
1 rows selected.
INSERT 문장에 의해 트리거가 수행되어 로그 테이블에 데이터가 저장되었음을 확인할 수 있다. 마찬가지로 UPDATE나 DELETE 문이 수행될 때에도 동작하는 트리거를 다음과 같이 생성할 수 있다.

CREATE OR REPLACE TRIGGER emp_logs_tr2
BEFORE DELETE ON employees
FOR EACH ROW
WHEN (new.employee_id > 0)
DECLARE
v_user varchar2(10);
BEGIN
SELECT USER
INTO v_user
FROM DUAL;
INSERT INTO emp_logs (employee_id, user_name, proc_type)
VALUES ( :new.employee_id, v_user, 'DELETE');
END;

HR 스키마에 있는 JOB_HISTORY란 테이블은 각 사원별로 부서와 직급이 변경된 내역의 정보를 가지고 있다. 다시 말한다면 임의의 한 사원의 부서(DEPARTMENT_ID)나 직급 (JOB_ID) 값이 변경되면 JOB_HISTORY 테이블에 데이터가 한 건 입력이 된다. 이러한 경 우, 트리거를 사용하면 쉽게 처리할 수 있다. HR 스키마에는 UPDATE_JOB_HISTORY 란 트리거가 있는데, 이는 EMPLOYEES 테이블  의 JOB_ID 컬럼값에 변경이 가해지면, 이 내용을 JOB_HISTORY 테이블에 기록하게 된다.

UPDATE_JOB_HISTORY란 트리거를 살펴보자.

CREATE OR REPLACE TRIGGER "HR".update_job_history
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
add_job_history(:old.employee_id, :old.hire_date, sysdate,
:old.job_id, :old.department_id);
END;

위 트리거는 EMPLOYEES 테이블의 JOB_ID, DEPARTMENT_ID 값을 변경할 경우 (UPDATE 문을 수행할 경우), ADD_JOB_HISTORY란 프로시져를 호출하여 JOB_HISTORY 테이블에 변경된 내역을 저장하고 있음을 보여주고 있다.
이렇듯 트리거는 특정한 경우 매우 유용하게 사용할 수 있는 데이터베이스 객체의 하나라 할 수 있다.