[MySQL] 트리거로 UPDATE 감지: BEFORE / AFTER 활용법 완전 정복 🛠️

MySQL에서는 특정 컬럼이 수정되기 전과 후의 값을 트리거를 통해 자동으로 감지할 수 있다.
이 기능은 로그 기록, 감사(Audit) 테이블 업데이트, 실시간 동기화 등에서 매우 유용하다.

✏️ 트리거(Trigger)란?

트리거(Trigger)는 테이블에서 INSERT, UPDATE, DELETE 같은 DML 이벤트가 발생할 때 자동으로 실행되는 SQL 블록이다.
특히 UPDATE 트리거는 변경 전(before)과 변경 후(after)의 값을 기반으로 조건 분기나 로깅을 처리할 수 있다.

⚙️ BEFORE UPDATE vs AFTER UPDATE 차이점

구분	설명
BEFORE UPDATE	실제 UPDATE 되기 전에 실행됨. 값 검증, 조건 필터링, 데이터 조정 가능
AFTER UPDATE	UPDATE가 완료된 직후 실행됨. 로그 저장, 관련 테이블 반영 등에 적합

두 트리거 모두 OLD.column_name과 NEW.column_name을 통해 이전 값과 이후 값을 비교할 수 있다.

반응형

🔍 실전 예제: BEFORE UPDATE로 특정 값 차단

DELIMITER //

CREATE TRIGGER trg_user_block_status
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF NEW.status = 'blocked' AND OLD.status != 'blocked' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '차단 상태로 직접 변경할 수 없습니다.';
  END IF;
END //

DELIMITER ;

해당 트리거는 사용자의 상태를 'blocked'로 직접 바꾸는 행위를 차단한다.
이처럼 정책 기반 필터링 로직을 트리거 안에서 구현할 수 있다.

📜 AFTER UPDATE로 변경 내역 로깅하기

CREATE TABLE user_log (
  user_id INT,
  prev_status VARCHAR(20),
  new_status VARCHAR(20),
  modified_at DATETIME
);

DELIMITER //

CREATE TRIGGER trg_user_status_log
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  IF OLD.status != NEW.status THEN
    INSERT INTO user_log (user_id, prev_status, new_status, modified_at)
    VALUES (OLD.id, OLD.status, NEW.status, NOW());
  END IF;
END //

DELIMITER ;

이 트리거는 사용자의 status가 바뀔 때마다 로그 테이블에 변경 내역을 기록한다.
운영 시스템에서는 이런 감사 로그 기능이 특히 중요하다.

반응형

📎 트리거 사용 시 유의사항

• 하나의 테이블에 트리거는 이벤트 종류별로 최대 1개씩만 등록 가능
• 트리거 내부에서 트랜잭션 제어(COMMIT, ROLLBACK)는 불가
• SIGNAL 구문을 사용해 예외를 명시적으로 발생시킬 수 있음
• 트리거의 남용은 성능 저하의 원인이 될 수 있으므로, 꼭 필요한 로직에만 사용

🧪 활용 팁: JSON 로그 저장

만약 로그 테이블이 많다면, 하나의 log_table에 JSON 형태로 값을 기록할 수도 있다.

INSERT INTO logs (table_name, event_type, data, created_at)
VALUES (
  'users',
  'UPDATE',
  JSON_OBJECT('user_id', OLD.id, 'from', OLD.status, 'to', NEW.status),
  NOW()
);

이런 방식은 다양한 테이블 로그를 하나로 통합 관리할 때 매우 유용하다.

📚 공식 문서 참고

공식 문서: MySQL 트리거 공식 가이드