[MySQL] 트리거로 테이블에 입력/수정/삭제 발생할 때 로그 남기기

MySQL에서 트리거(trigger)는 테이블에 삽입(insert), 수정(update), 삭제(delete) 등의 이벤트가 발생했을 때 자동으로 무언가를 실행시키는 것을 의미합니다. 예를 들어, 테이블의 내용이 수정되었을 때 누가, 언제 수정했는지, 원래 데이터는 무엇이었는지 등의 기록을 저절로 다른 테이블에 삽입해준다면 나중에 문제가 발생했을 때 추적하기가 좋을 것입니다.

 

우선 예시를 위해 epl 선수들의 골 수(2022-10-24 기준)를 담고 있는 간단한 테이블을 만들었습니다. 테이블 이름은 epl입니다. 

 

 

이 테이블에 수정 또는 삭제가 발생했을 때 epl_log 테이블에 그 기록을 트리거로 남기도록 하겠습니다. 먼저 epl_log 테이블을 만들겠습니다. 

 

CREATE TABLE epl_log (
    name VARCHAR(20),
    goal INT,
    modType CHAR(2),
    modDatetime DATETIME NOT NULL DEFAULT current_timestamp,
    modUser VARCHAR(256)
);

 

이 테이블에 원래 epl 테이블에 있었던 name, goal이 무엇이었는지를 남길 것이고 추가로 수정인지 삭제인지(modType), 수정 일시(modDatetime), 수정자(modUser)의 기록을 남길 것입니다. 

 

트리거 생성

이제 트리거를 생성해보겠습니다. 먼저, update시 발현되는 트리거를 생성하겠습니다. 

 

DELIMITER $$
CREATE TRIGGER epl_log_UpdateTrg
    AFTER UPDATE
    ON epl
    FOR EACH ROW
BEGIN
    INSERT INTO epl_log (name, goal, modType, modUser) VALUES (OLD.name, OLD.goal, '수정', CURRENT_USER());
END $$
DELIMITER ;

 

AFTER UPDATE를 주목하십시오. update 후에 BEGIN END 안에 있는 SQL 문을 실행하겠다는 뜻입니다. 그리고 OLD라는 것을 볼 수 있는데 원래 기록을 잠시 담고 있는 임시 테이블과 같은 개념입니다.

 

이번에는 delete 트리거를 생성하겠습니다. 

 

DELIMITER $$
CREATE TRIGGER epl_log_DeleteTrg
	AFTER DELETE
    ON epl
    FOR EACH ROW
BEGIN
	INSERT INTO epl_log (name, goal, modType, modUser) VALUES (OLD.name, OLD.goal, '삭제', CURRENT_USER());
END $$
DELIMITER ;

 

AFTER UPDATE를 AFTER DELETE로 바꿔줬습니다. 물론 트리거의 이름도 바뀌었고 modType에는 '삭제'가 들어가도록 했습니다. MySQL Workbench에서 해당 테이블을 클릭해보면 Triggers 항목 안에 방금 생성한 트리거들이 포함되어 있을 것입니다. 혹시 안 보이면 새로고침해보세요. 

 

 

트리거 사용

이제 epl 테이블에 수정과 삭제가 발생하게 하여 트리거가 사용되게 해보겠습니다. 우선 엘링 홀란드를 손흥민으로 바꿔보겠습니다. 또한 해리 케인 행을 삭제해보겠습니다. 

 

UPDATE epl SET name = '손흥민' WHERE name = '엘링 홀란';
DELETE FROM epl WHERE name = '해리 케인';

 

epl 테이블은 다음과 같이 잘 수정되었습니다. 

 

 

하지만 지금 우리가 관심 있는 것은 epl_log 테이블의 내용입니다.

 

SELECT * FROM epl_log;

 

보시는 것처럼 로그가 잘 남은 것을 확인할 수 있습니다. 

 

 

언제 누구에 의해 어떤 데이터가 수정 또는 삭제되었는지를 이런 식으로 기록해둔다면, 나중에 문제가 발생했을 때 처리가 조금 더 쉬워질 것입니다.  

 

관련 글

- [MariaDB] 데이터를 insert한 시각을 저절로 기록되게 하려면?