oracle - 트리거(trigger)
트리거(Trigger)란?
트리거(Trigger)의 원 의미는 ‘방아쇠’이다. 데이터베이스에서 트리거는 바로 이러한 의미를
유추한다면 어떤 역할을 하는지 쉽게 짐작할 수 있을 것이다.
데이터베이스에서 트리거는 특정 이벤트(event)나 DDL, DML 문장이 실행되었을 때, 자동
적으로 어떤 동작(Operation)이나 처리를 수행하도록 하는 데이터베이스 객체의 하나이다.
일반적으로 트리거는 임의의 테이블에 데이터를 추가(insert), 삭제(delete), 갱신(delete) 할
때, 또 다른 테이블에 있는 데이터를 자동적으로 조작할 경우에 사용된다. 이러한 경우 가
장 흔히 사용되는 형태가 로그(log) 정보를 기록하는 경우이다.
예를 들어보자.
신규 사원을 등록하려면 employees 테이블에 데이터를 한 건 입력해야 한다. 그런데, 신규
사원을 등록(입사처리)하거나 삭제(퇴사처리)할 경우, 누가, 언제 작업 했는지를 기록해야
한다고 하자. 이러한 경우 일반적으로 로그 테이블을 별도로 생성하여 그 정보를 기록해 두
는데, 입사처리를 수행한 뒤 로그 테이블에 또 다시 정보를 기록하는 일은 단순한 작업이면
서도 번거로운 작업이다. 따라서 로그 테이블에 정보를 저장하는 작업을 자동화 하는 것이
여러 모로 좋은 방법이라 할 수 있다. 물론 이러한 로그 정보처리는 어플리케이션에서 처리
할 수도 있지만, 데이터베이스 상에서 직접 처리한다면 훨씬 더 효율적일 것이다. (마치 저
장 프로시져를 사용하거나 테이블의 컬럼에 디폴트 속성을 넣는 것처럼) 바로 이러한 경우
에 트리거를 사용하여 처리하게 된다.
2. 트리거의 사용
간단한 예를 들어, 트리거에 대해 좀 더 자세히 살펴보도록 하자.
먼저 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 OR REPLACE 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
테이블에 변경된 내역을 저장하고 있음을 보여주고 있다.
이렇듯 트리거는 특정한 경우 매우 유용하게 사용할 수 있는 데이터베이스 객체의 하나라
할 수 있다.