트리거 개념

트리거(trigger)는 특수 유형의 저장 프로시저로, DB의 이벤트 기반으로 자동 실행됩니다. 일반 저장 프로시저와 달리 명시적 호출 없이 지정된 데이터 변화가 발생하면 활성화됩니다.

트리거의 주요 기능

1. 데이터 변환 이전 유효성 검증
2. 트리거 실행 오류 시 모든 변경 사항 자동 롤백
3. DDL(Data Definition Language) 작업 감지 및 대응 (DBMS 종속적)

장단점 분석

장점

• 연관 테이블 간 자동 업데이트 가능(계단식 변경)
• 데이터 무결성 강제 적용

단점

• DB 구조 종속성 증가 및 유지보수 복잡화
• 애플리케이션 레이어에서의 데이터 제어 불확실성

트리거 생성 구문

CREATE TRIGGER trigger_name 
   [BEFORE|AFTER] [INSERT|UPDATE|DELETE] 
   ON table_name FOR EACH ROW
BEGIN
   -- 실행 로직
END;

핵심 매개변수

트리거 대상

ON 절에 명시된 테이블의 행 단위 바인딩

활성화 시점

• BEFORE: 데이터 변경 이전 상태
• AFTER: 데이터 변경 이후 상태

활성화 이벤트

• INSERT: 삽입 작업
• UPDATE: 갱신 작업
• DELETE: 삭제 작업

주요 제약사항

단일 테이블 내 특정 이벤트 시점(예: AFTER INSERT) 당 최대 하나의 트리거 허용. 따라서 테이블 당 최대 6개 트리거 존재 가능: BEFORE/AFTER 각각 INSERT/UPDATE/DELETE 이벤트 대상.

트리거 관리 명령어

트리거 목록 확인

SHOW TRIGGERS;

생성 구문 확인

SHOW CREATE TRIGGER trigger_name;

트리거 삭제

DROP TRIGGER trigger_name;

데이터 상태 추적 객체

NEW와 OLD 키워드는 트리거 내부에서 행 변경 전후 상태를 캡처합니다.

-- 컬럼 값 접근
SELECT OLD.column_name, NEW.column_name;

지원 범위

• INSERT: NEW 키워드만 유효
• DELETE: OLD 키워드만 유효
• UPDATE: NEW/OLD 모두 사용 가능

재고 관리 자동화 예시

스키마 구조

-- 상품 테이블
CREATE TABLE inventory (
  item_id INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(30) NOT NULL,
  stock_count INT DEFAULT 0
);

-- 주문 테이블
CREATE TABLE customer_orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  order_ref VARCHAR(55) NOT NULL UNIQUE,
  quantity INT CHECK(quantity > 0),
  item_id INT,
  FOREIGN KEY (item_id) REFERENCES inventory(item_id)
);

데이터 초기화

INSERT INTO inventory VALUES 
  (NULL, '스마트폰', 100),
  (NULL, '노트북', 80);
  
SELECT * FROM inventory;

트리거 구현

주문 전 재고 검증

CREATE TRIGGER tr_verify_stock 
  BEFORE INSERT ON customer_orders 
  FOR EACH ROW
BEGIN
  DECLARE current_inv INT DEFAULT 0;
  -- 재고 조회
  SELECT stock_count INTO current_inv 
    FROM inventory 
    WHERE item_id = NEW.item_id;
  -- 재고 부족 시 주문 차단
  IF current_inv < NEW.quantity THEN
    -- 의도적 오류 발생
    SIGNAL SQLSTATE '45000' 
      SET MESSAGE_TEXT = '재고 부족';
  END IF;
END;

주문 확정 후 재고 차감

CREATE TRIGGER tr_update_inv 
  AFTER INSERT ON customer_orders 
  FOR EACH ROW
BEGIN
  UPDATE inventory 
    SET stock_count = stock_count - NEW.quantity 
    WHERE item_id = NEW.item_id;
END;

시나리오 테스트

-- 정상 주문
INSERT INTO customer_orders VALUES 
  (NULL, 'ORD20240601X', 3, 2);
  
-- 재고 초과 주문 (실패 예상)
INSERT INTO customer_orders VALUES 
  (NULL, 'ORD20240602Y', 200, 1);

-- 결과 확인
SELECT * FROM customer_orders;
SELECT * FROM inventory;