오라클 TRIGGER를 활용한 데이터 무결성 유지

오라클 DB에서 **TRIGGER(트리거)**는 특정 이벤트(INSERT, UPDATE, DELETE)가 발생했을 때 자동으로 실행되는 PL/SQL 코드 블록입니다. 데이터 무결성을 유지하고, 특정 규칙을 강제하기 위해 트리거를 활용하면 유용합니다. 이번 글에서는 트리거의 기본 개념부터 데이터 무결성 유지에 유용한 실무 예제까지 다룹니다.

---

1. TRIGGER의 개념

트리거는 테이블이나 뷰에 대해 정의되며, 특정 작업이 수행될 때 자동으로 실행됩니다. 주로 데이터 무결성을 유지하거나, 기록 로그를 작성하는 데 사용됩니다.

TRIGGER의 주요 특징:

• 자동 실행: 트리거는 명시적인 호출 없이 지정된 조건에서 실행됩니다.
• 테이블 수준, 행 수준에서 작동 가능.
• 데이터의 삽입, 수정, 삭제 시 실행.

기본 구문:

CREATE OR REPLACE TRIGGER 트리거_이름
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON 테이블명
[ FOR EACH ROW ]
BEGIN
   -- 트리거에서 실행할 PL/SQL 코드
END;

---

2. TRIGGER의 유형

1. 타이밍:
   • BEFORE: 작업 실행 전에 트리거 실행.
   • AFTER: 작업 실행 후에 트리거 실행.
2. 이벤트 유형:
   • INSERT: 데이터 삽입 시 실행.
   • UPDATE: 데이터 수정 시 실행.
   • DELETE: 데이터 삭제 시 실행.
3. 범위:
   • Statement-Level Trigger: 작업 단위로 실행.
   • Row-Level Trigger: 각 행에 대해 실행(FOR EACH ROW 사용).

---

3. 데이터 무결성을 위한 TRIGGER 작성 예제

예제 1: 직원의 급여가 특정 한도를 초과하지 않도록 강제

employees 테이블에서 급여가 10,000을 초과하지 않도록 제한하는 트리거입니다.

CREATE OR REPLACE TRIGGER trg_check_salary
BEFORE INSERT OR UPDATE OF salary
ON employees
FOR EACH ROW
BEGIN
   IF :NEW.salary > 10000 THEN
      RAISE_APPLICATION_ERROR(-20001, '급여는 10,000을 초과할 수 없습니다.');
   END IF;
END;
/

• 설명:
  • :NEW는 새로 입력되거나 수정된 데이터 값.
  • RAISE_APPLICATION_ERROR를 사용하여 사용자 정의 오류 메시지를 반환.

---

예제 2: 삭제 방지 트리거

중요한 데이터를 실수로 삭제하지 못하도록 방지합니다.
예를 들어, departments 테이블에서 특정 부서는 삭제되지 않도록 설정합니다.

CREATE OR REPLACE TRIGGER trg_prevent_delete
BEFORE DELETE
ON departments
FOR EACH ROW
BEGIN
   IF :OLD.department_id = 10 THEN
      RAISE_APPLICATION_ERROR(-20002, '부서 ID 10은 삭제할 수 없습니다.');
   END IF;
END;
/

• 설명:
  • :OLD는 기존 데이터 값.
  • 특정 조건(department_id = 10)에 해당하는 데이터 삭제를 차단.

---

예제 3: 자동 로그 기록

employees 테이블에서 데이터 변경 시 변경 내역을 audit_log 테이블에 기록합니다.

CREATE OR REPLACE TRIGGER trg_audit_log
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH ROW
BEGIN
   INSERT INTO audit_log (log_id, operation, employee_id, change_date)
   VALUES (audit_log_seq.NEXTVAL,
           CASE
              WHEN INSERTING THEN 'INSERT'
              WHEN UPDATING THEN 'UPDATE'
              WHEN DELETING THEN 'DELETE'
           END,
           :OLD.employee_id,
           SYSDATE);
END;
/

• 설명:
  • INSERTING, UPDATING, DELETING은 트리거 동작 상태를 나타냄.
  • audit_log 테이블은 변경 사항을 저장하기 위한 별도 테이블.

---

4. 실무에서 활용 가능한 TRIGGER 예제

예제 1: 재고 관리 자동 업데이트
orders 테이블에 주문이 들어오면 products 테이블의 재고량을 자동으로 줄입니다.

CREATE OR REPLACE TRIGGER trg_update_stock
AFTER INSERT
ON orders
FOR EACH ROW
BEGIN
   UPDATE products
   SET stock_quantity = stock_quantity - :NEW.quantity
   WHERE product_id = :NEW.product_id;
END;
/

• 설명:
  • 주문 수량만큼 제품 재고를 차감.

---

예제 2: 데이터 생성일 자동 추가
employees 테이블에 새 데이터를 삽입할 때 created_date 값을 자동으로 설정합니다.

CREATE OR REPLACE TRIGGER trg_set_created_date
BEFORE INSERT
ON employees
FOR EACH ROW
BEGIN
   :NEW.created_date := SYSDATE;
END;
/

• 설명:
  • SYSDATE를 사용하여 현재 날짜와 시간을 자동 입력.

---

예제 3: 월별 매출 합산 자동 계산
sales 테이블에서 새로운 판매 데이터가 추가될 때 monthly_sales 테이블을 자동 업데이트합니다.

CREATE OR REPLACE TRIGGER trg_update_monthly_sales
AFTER INSERT
ON sales
FOR EACH ROW
BEGIN
   MERGE INTO monthly_sales ms
   USING (SELECT TO_CHAR(:NEW.sale_date, 'YYYY-MM') AS sale_month, :NEW.amount AS sale_amount FROM DUAL) src
   ON (ms.sale_month = src.sale_month)
   WHEN MATCHED THEN
      UPDATE SET ms.total_sales = ms.total_sales + src.sale_amount
   WHEN NOT MATCHED THEN
      INSERT (sale_month, total_sales)
      VALUES (src.sale_month, src.sale_amount);
END;
/

• 설명:
  • MERGE 문을 활용하여 월별 판매 데이터를 삽입하거나 업데이트.

---

5. TRIGGER 작성 시 주의사항

1. 성능 이슈:
   • 트리거는 자동으로 실행되므로 잘못 설계하면 성능 저하를 초래할 수 있습니다.
   • 특히 대량 데이터 작업 시 주의해야 합니다.
2. 디버깅:
   • 오류 발생 시 트리거 디버깅이 어렵기 때문에 로그 테이블을 활용하거나 트리거 코드를 꼼꼼히 검토해야 합니다.
3. 순환 참조 방지:
   • 트리거가 또 다른 트리거를 호출하여 무한 루프가 발생하지 않도록 설계해야 합니다.
4. 업데이트 충돌:
   • 다중 사용자가 동시에 데이터를 변경할 경우 트리거가 의도치 않게 동작할 수 있습니다.

---

결론

트리거는 데이터 무결성을 유지하고, 데이터 변경 작업을 자동화하는 강력한 도구입니다. 특히 업무 로직의 규칙을 강제하거나, 데이터 변경 이력을 관리하는 데 효과적입니다. 하지만 성능 및 유지보수를 고려하여 적절히 설계하는 것이 중요합니다. 위의 예제를 참고하여 실무에서 트리거를 효과적으로 활용해 보세요!