오라클 트리거의 작성법과 다양한 유형의 예제

오라클 트리거(Trigger)는 데이터베이스에서 특정 이벤트(INSERT, UPDATE, DELETE 등)가 발생했을 때 자동으로 실행되는 PL/SQL 블록입니다. 트리거는 데이터 무결성 유지, 로깅, 감사(Audit) 등의 작업에 주로 사용됩니다. 이번 글에서는 오라클 트리거의 개념과 작성법, 다양한 유형의 예제를 상세히 설명하겠습니다.

---

1. 트리거란 무엇인가?

1.1 트리거의 정의

오라클 트리거는 테이블이나 뷰에서 데이터 조작 언어(DML) 작업이 실행될 때 자동으로 실행되는 특별한 유형의 저장 프로시저입니다. 트리거는 사전에 정의된 조건과 특정 이벤트에 반응하여 동작합니다.

1.2 트리거의 특징

• 특정 테이블이나 뷰에 연결되어 동작합니다.
• DML 작업(INSERT, UPDATE, DELETE) 또는 DDL 작업(CREATE, DROP 등)에 대해 실행됩니다.
• COMMIT 또는 ROLLBACK 명령어는 사용할 수 없습니다.
• 데이터베이스 레벨에서 자동 실행되므로 사용자 개입이 필요 없습니다.

1.3 트리거의 장점

1. 데이터 무결성 유지: 테이블 간의 연관된 데이터를 일관성 있게 유지.
2. 자동화 작업: 이벤트 기반으로 로그 기록, 데이터 조정 등 반복 작업 자동화.
3. 보안 강화: 특정 조건에서 데이터 조작을 방지하거나 기록 가능.

---

2. 트리거의 작성법

트리거를 작성하려면 CREATE TRIGGER 명령어를 사용합니다.

2.1 기본 문법

CREATE [OR REPLACE] TRIGGER 트리거이름
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT [OR] | UPDATE [OR] | DELETE }
ON 테이블이름
[FOR EACH ROW]
WHEN (조건)
BEGIN
    -- 트리거의 작업 내용
END;
/

2.2 주요 키워드

• BEFORE | AFTER | INSTEAD OF: 트리거가 실행될 시점.
  • BEFORE: DML 작업 이전에 실행.
  • AFTER: DML 작업 이후에 실행.
  • INSTEAD OF: 뷰(View)에서 DML 작업 대신 실행.
• INSERT | UPDATE | DELETE: 트리거가 실행될 작업 유형.
• FOR EACH ROW: 트리거가 각 행(Row)마다 실행됨을 지정.
• WHEN(조건): 특정 조건에서만 실행.

---

3. 트리거의 유형과 예제

3.1 BEFORE INSERT 트리거

INSERT 작업 전에 실행됩니다.

예제
사원의 급여가 3,000보다 작을 경우 최소 급여로 3,000을 설정:

CREATE OR REPLACE TRIGGER before_insert_salary
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 3000 THEN
        :NEW.salary := 3000;
    END IF;
END;
/

설명

• :NEW는 삽입될 데이터를 나타냅니다.
• 조건에 따라 데이터가 변경됩니다.

---

3.2 AFTER UPDATE 트리거

UPDATE 작업 이후에 실행됩니다.

예제
사원의 급여가 변경될 때 로그 테이블에 기록:

CREATE OR REPLACE TRIGGER after_update_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_log (employee_id, old_salary, new_salary, updated_date)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

설명

• :OLD는 이전 데이터를, :NEW는 변경된 데이터를 나타냅니다.
• 급여가 변경될 때마다 기록됩니다.

---

3.3 BEFORE DELETE 트리거

DELETE 작업 전에 실행됩니다.

예제
특정 조건에 따라 삭제를 차단:

CREATE OR REPLACE TRIGGER before_delete_employees
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    IF :OLD.job_id = 'MANAGER' THEN
        RAISE_APPLICATION_ERROR(-20001, 'MANAGER는 삭제할 수 없습니다.');
    END IF;
END;
/

설명

• RAISE_APPLICATION_ERROR를 사용하여 사용자 정의 오류를 발생시킵니다.
• MANAGER 직책을 가진 사원을 삭제하려 할 경우 오류가 발생합니다.

---

3.4 INSTEAD OF 트리거

뷰(View)에 대해 DML 작업을 실행할 때 사용됩니다.

예제
복합 뷰에서 UPDATE 작업 구현:

CREATE OR REPLACE VIEW employee_view AS
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

CREATE OR REPLACE TRIGGER instead_of_update_employee_view
INSTEAD OF UPDATE ON employee_view
FOR EACH ROW
BEGIN
    UPDATE employees
    SET first_name = :NEW.first_name
    WHERE employee_id = :OLD.employee_id;
END;
/

설명

• 복합 뷰에서는 기본적으로 DML 작업이 허용되지 않으므로 INSTEAD OF 트리거를 사용해 구현합니다.

---

3.5 복합 트리거

INSERT, UPDATE, DELETE 작업을 모두 처리할 수 있는 트리거입니다.

예제
모든 DML 작업에 대해 로그 기록:

CREATE OR REPLACE TRIGGER log_all_dml
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO audit_log (action, employee_id, log_date)
        VALUES ('INSERT', :NEW.employee_id, SYSDATE);
    ELSIF UPDATING THEN
        INSERT INTO audit_log (action, employee_id, log_date)
        VALUES ('UPDATE', :NEW.employee_id, SYSDATE);
    ELSIF DELETING THEN
        INSERT INTO audit_log (action, employee_id, log_date)
        VALUES ('DELETE', :OLD.employee_id, SYSDATE);
    END IF;
END;
/

설명

• INSERTING, UPDATING, DELETING 키워드를 사용해 작업 유형을 구분합니다.

---

3.6 트리거에서 동적 SQL 사용

동적 SQL을 실행할 수 있습니다.

예제
삭제 작업 시 데이터 백업:

CREATE OR REPLACE TRIGGER backup_before_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO employees_backup VALUES (:1, :2, :3, :4)'
    USING :OLD.employee_id, :OLD.first_name, :OLD.last_name, :OLD.salary;
END;
/

---

4. 트리거 사용 시 주의사항

1. 성능 문제
   트리거는 자동으로 실행되므로 지나치게 복잡한 작업을 정의하면 성능에 영향을 줄 수 있습니다.
2. 디버깅 어려움
   트리거의 동작은 사용자에게 명시적으로 보이지 않아 디버깅이 어렵습니다.
3. COMMIT 제한
   트리거 내에서 COMMIT 또는 ROLLBACK을 사용할 수 없습니다.
4. 순환 참조 방지
   트리거가 자기 자신을 호출하거나 무한 루프를 발생시키지 않도록 주의해야 합니다.

---

5. 결론

오라클 트리거는 데이터 무결성 유지와 보안, 자동화 작업에서 강력한 도구입니다. 트리거를 적절히 활용하면 데이터베이스 관리가 효율적이고 체계적이 됩니다. 하지만 성능 문제와 디버깅의 어려움을 고려하여 필요할 때만 사용하는 것이 좋습니다.

위의 예제를 참고하여 트리거를 설계하고, 데이터베이스를 더욱 안전하고 효율적으로 운영해보세요!