MySQL 운영 DB에서 안전하게 트리거 우회하기 (내 세션만 일시정지하는 법)

 

DB를 운영하다보면 가끔씩 데이터를 수정해야하는데 트리거가 걸려있어 함부로 수정할수 없는 경우가 생기곤 합니다.

트리거가 걸린상태에서 예를들어 대량의 업데이트나 삭제처리등이 된다면 락이 걸리거나 성능에 엄청난 부하가 가서 문제가 발생하게 됩니다.

그렇기 때문에 이럴때는 트리거를 끄고 실행을 해야하는데 트리거를 꺼버리면 많은 사이드이펙트가 발생하고 실제 운영에도 문제가 생기기 때문에 해당방법은 절대 하면 안되는 방법입니다.

그렇다면 어떻게 실제 시스템엔 영향을 안가게하고  트리거의 영향없이 쿼리를 실행할수 있을까요?

---

1. MySQL의 한계와 '스위치' 설계

결론부터 말하자면 아쉽게도 MySQL은 Oracle이나 PostgreSQL처럼 ALTER TRIGGER DISABLE 같은 명령어를 세션 단위로 지원하지 않습니다. 그래서 실무에서는 트리거를 생성할 때부터 '우회 스위치' 역할을 하는 세션 변수 로직을 미리 심어둡니다.

이 방식은 내 세션에서만 트리거를 끄기 때문에, 다른 사용자가 발생시키는 일반적인 트랜잭션에는 전혀 영향을 주지 않는 가장 안전한 방법입니다.

---

2. 실습 환경 구축

직접 테스트해 보실 수 있도록 상품 마스터와 주문 상세 테이블, 그리고 로그 테이블을 구성해 보겠습니다.
(한글 깨짐 방지를 위해 utf8mb4 설정을 추가해야합니다.)

-- 1. 상품 마스터 테이블 (한글 설정 추가)
CREATE TABLE PRODUCTS (
    PROD_ID INT PRIMARY KEY,
    PROD_NAME VARCHAR(100),
    CATEGORY VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 한글 지원을 위해 추가

-- 2. 주문 상세 테이블 (한글 설정 추가)
CREATE TABLE ORDER_DETAILS (
    ORDER_ID INT AUTO_INCREMENT PRIMARY KEY,
    PROD_ID INT,
    STATUS VARCHAR(20) DEFAULT 'PENDING',
    ORDER_DATE DATETIME,
    FOREIGN KEY (PROD_ID) REFERENCES PRODUCTS(PROD_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 로그 테이블도 잊지 마세요!
CREATE TABLE ORDER_LOGS (
    LOG_ID INT AUTO_INCREMENT PRIMARY KEY,
    ORDER_ID INT,
    OLD_STATUS VARCHAR(20),
    NEW_STATUS VARCHAR(20),
    CREATE_DATE DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---

3. 핵심: 우회 로직이 포함된 트리거 작성

위에서 테이블을 만들었으면 트리거 문구 안에 @DISABLE_TRIGGER라는 세션 변수 체크 로직을 넣는 것이 핵심입니다.
이 방법은 DISABLE_TRIGGER 변수를 통해서 스위치 값을 입력받아 아래의 로직을 동작시키지 않는 간단한 반환로직입니다.

이방법을 사용해야하는 이유는 앞서 말했듯이 MYSQL은 해당세션에서만 트리거를 실행 안되게끔 하는 명령어나 설정이 없기 때문에 트리거를 생성할때 아래 로직을 꼭 추가해주는 것이 추후 운영상에서도 용이합니다.

이 트리거가 제대로 동작하면 STATUS값이 변경될때마다 변경된값이 ORDER_LOGS에 저장될 것입니다.

DELIMITER $$

CREATE TRIGGER TRG_ORDER_UPDATE
AFTER UPDATE ON ORDER_DETAILS
FOR EACH ROW
BEGIN
    -- [핵심] 세션 변수 체크: 1이면 아무것도 안 하고 종료
    IF @DISABLE_TRIGGER IS NULL OR @DISABLE_TRIGGER <> 1 THEN
        
        -- 상태(STATUS)가 변경되었을 때만 로그를 남기도록 설정
        IF OLD.STATUS <> NEW.STATUS THEN
            INSERT INTO ORDER_LOGS (ORDER_ID, OLD_STATUS, NEW_STATUS, CREATE_DATE)
            VALUES (
                NEW.ORDER_ID, 
                OLD.STATUS, 
                NEW.STATUS, 
             NOW());
        END IF;
        
    END IF;
END$$

DELIMITER ;

---

4. 데이터 세팅

아래 쿼리로 데이터를 테이블에 각각 할당해줍니다.

INSERT INTO PRODUCTS VALUES (1, '아이폰 16', '전자제품'), (2, '갤럭시 S24', '전자제품');
INSERT INTO ORDER_DETAILS (PROD_ID, STATUS, ORDER_DATE) VALUES 
(1, 'PENDING', '2025-01-01 10:00:00'),
(1, 'PENDING', '2025-01-01 11:00:00'),
(2, 'PENDING', '2025-01-01 12:00:00'),
(2, 'SHIPPED', '2025-01-01 13:00:00'); -- 이미 배송된 건 (제외 대상)

---

5.업데이트로 인한 트리거 동작

이제 아래 업데이트문으로 주문의 상태를 'SHIPPED'로 변경해보겠습니다.

 

UPDATE `practice`.`order_details` SET `STATUS`='SHIPPED' WHERE  `ORDER_ID`=1;

 

1을 SHIPPED로 변경했다

위처럼 변경을 하면 아래의 ORDER_LOGS에 자동으로 어떤값에서 어떤값으로 변경되었는지 트리거가 동작하여 데이터가 쌓이게 됩니다.

그렇다면 위에서 만든 스위치변수를 사용해서 업데이트를 한다면 트리거가 동작할지 테스트해보겠습니다.

반응형

---

6.내 세션에서만 트리거 실행

여기서 중요한게 내가 세팅변수를 설정한 세션에서만 트리거가 동작이 되는지 확인하는게 핵심입니다.

START TRANSACTION;

-- [핵심] 1. 내 세션에서만 트리거 비활성화
SET @DISABLE_TRIGGER = 1;

-- 2.업데이트
UPDATE `practice`.`order_details` SET `STATUS`='PENDING' WHERE  `ORDER_ID`=1;

-- 3. 결과 확인 (로그 테이블에 기록이 없는지 체크)
SELECT * FROM ORDER_LOGS; -- 트리거가 작동하지 않아 로그가 비어있어야 함

COMMIT;

위 업데이트문을 실행했으면 정상적으로는 ORDER_LOGS에 데이터가 쌓여야하는데 아래와 같이 제대로 쌓이지 않는 것을 확인할수 있습니다.

1번창

그렇다면 다른 창을 하나 더 켜서 과연 거기서는 문제없이 트리거가 작동 되는지를 확인해서
지금 켜둔 이 창의 세션에서만 트리거가 동작하지 않는다는 것을 보겠습니다.

2번창

위에서 볼수 있다싶이 1번창에서 DISABLE_TRIGGER로 인해서 실행되지 않은 트리거가 2번창에선 제대로 실행된 것을 볼수 있습니다.

---

7.어떻게 세션에서만 가능할까?

이쯤되면 왜 세션에서만 이게 가능한지 궁금해집니다.

원인은 바로 MySQL에서 @로 시작하는 변수는 '세션 변수'입니다.

• 독립 공간: 이 변수는 DB 서버 전체가 아니라, 현재 연결된 특정 세션에만 귀속됩니다.
• 나만의 스위치: 내가 @DISABLE_TRIGGER = 1을 실행하는 것은 내 방의 전등 스위치를 내리는 것과 같습니다. 옆 방(다른 사용자 세션)의 전등은 여전히 켜져 있으며, 내 변수 설정에 아무런 영향을 받지 않습니다.

---

8.마무리

1. 세션 독립성: 내가 설정한 변수는 내 쿼리 창에서만 유효하며, 서비스 중인 다른 사용자에게는 전혀 영향을 주지 않습니다.
2. 안전성: 트리거를 DROP 했다가 다시 생성하는 번거로움과 리스크(실수 등)가 없습니다.
3. 확장성: 마이그레이션, 배치 작업, 긴급 데이터 수정 등 다양한 상황에서 즉시 활용 가능합니다.

실행 전에는 항상 SELECT 문으로 업데이트 대상을 먼저 확인하고, 작업이 끝난 후에는 SET @DISABLE_TRIGGER = 0;을 실행하거나 커넥션을 종료하여 '안전핀'을 다시 꽂는 습관을 들이시길 권장합니다.

단순히 쿼리를 잘 짜는 것을 넘어, 시스템의 전체적인 부하와 사이드 이펙트를 고려하는 설계가 중요합니다. 오늘 포스팅이 여러분의 안전한 DB 운영에 큰 도움이 되었기를 바랍니다!