[MySQL] 트리거로 DELETE 감지: BEFORE / AFTER 완벽 가이드 🗑️

MySQL에서 데이터를 삭제할 때 무심코 날려버리는 레코드가 사실은 매우 중요한 정보일 수 있다.
이를 대비해 DELETE 이벤트를 자동으로 감지하고 후속 처리를 자동화하는 방법이 바로 트리거(Trigger)이다.

🔍 트리거 기본 개념 다시 보기

트리거(Trigger)는 특정 테이블에서 INSERT, UPDATE, DELETE 명령이 실행될 때 자동으로 실행되는 SQL 코드 블록이다.
DELETE와 관련된 트리거는 다음과 같이 구분된다.

트리거 종류	실행 시점	활용 목적
BEFORE DELETE	삭제 전	삭제 조건 검증, 로그 기록, 삭제 취소 처리 등
AFTER DELETE	삭제 후	관련 테이블 동기화, 감사(Audit) 기록 등

반응형

✅ BEFORE DELETE 트리거 예제: 삭제 차단

사용자가 중요 고객 데이터를 삭제하는 것을 방지하고 싶을 때 사용할 수 있다.

DELIMITER //

CREATE TRIGGER trg_protect_vip_delete
BEFORE DELETE ON customers
FOR EACH ROW
BEGIN
  IF OLD.grade = 'VIP' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'VIP 고객은 삭제할 수 없습니다.';
  END IF;
END //

DELIMITER ;

해당 트리거는 grade가 'VIP'인 고객을 삭제하려고 할 경우, 명시적인 예외를 발생시켜 삭제를 차단한다.

📋 AFTER DELETE 트리거 예제: 삭제 로그 저장

삭제된 데이터를 별도의 로그 테이블에 보관하고 싶을 때 유용하다.

CREATE TABLE deleted_customers_log (
  id INT,
  name VARCHAR(100),
  deleted_at DATETIME
);

DELIMITER //

CREATE TRIGGER trg_log_deleted_customers
AFTER DELETE ON customers
FOR EACH ROW
BEGIN
  INSERT INTO deleted_customers_log (id, name, deleted_at)
  VALUES (OLD.id, OLD.name, NOW());
END //

DELIMITER ;

이렇게 하면 삭제된 고객 정보가 자동으로 로그 테이블에 저장되어 사후 분석, 데이터 복구 등에 활용할 수 있다.

반응형

🧩 트리거 활용 팁: JSON 로그 형식으로 남기기

로그를 JSON 형식으로 통합 관리하는 것도 매우 좋은 방법이다.

CREATE TABLE delete_logs (
  table_name VARCHAR(50),
  log_data JSON,
  deleted_at DATETIME
);

DELIMITER //

CREATE TRIGGER trg_delete_customer_json
AFTER DELETE ON customers
FOR EACH ROW
BEGIN
  INSERT INTO delete_logs (table_name, log_data, deleted_at)
  VALUES (
    'customers',
    JSON_OBJECT('id', OLD.id, 'name', OLD.name, 'grade', OLD.grade),
    NOW()
  );
END //

DELIMITER ;

이렇게 하면 다양한 테이블의 삭제 로그를 하나의 테이블에서 JSON으로 관리할 수 있다.

⚠️ 주의사항 정리

• 트리거 안에서는 트랜잭션 제어 불가능 (COMMIT, ROLLBACK 등)
• 트리거는 테이블당 같은 이벤트(BEFORE DELETE 등)는 1개만 존재 가능
• SIGNAL SQLSTATE 구문은 MySQL 5.5 이후부터 지원되며 예외 처리에 필수
• 트리거가 너무 복잡하거나 많으면 퍼포먼스 이슈가 발생할 수 있으므로 신중하게 구성해야 한다

💡 실무 활용 팁

• 삭제가 중요한 테이블은 항상 AFTER DELETE 로그 트리거를 만들어두는 것이 좋다
• 예기치 않은 삭제 사고 예방을 위해 BEFORE DELETE 조건 검증을 습관화해야 한다
• 로그 테이블은 파티셔닝이나 주기적인 정리 작업이 필요하다

📚 참고 자료

공식 문서: MySQL 트리거 문서