[ Database - Intermediate ] - SQL trigger

SQL에서 Trigger 란

데이터에 변경이 생겼을 때에 DB에 insert, update, delete가 발생했을 떄 이것이 계기가 되어 자동적으로 실행되는 프로시저(procedure)를 의미하게 됩니다. 바로 예시를 들어서 살펴보도록 하겠습니다. 만약 사용자의 닉네임 변경 이력을 저장하는 트리거를 작성해 보고 싶다고 해 보겠습니다.

 

위와같은 상황이라고 할 때, USERS_LOG에 기존 닉네임을 저장해야 합니다.

 

delimiter $$
CREATE TRIGGER log_user nickname_trigger
BEFORE UPDATE
ON users FOR EACH ROW
BEGIN
    insert into users_log values(OLD.id, OLD.nickname, now());
END
$$
delimiter ;

이렇게 작성해 주면 되는데요. BEFORE UPDATE 즉 업데이트 쿼리 이전에, ON users FOR EACH ROW 변경되는 로우 전부를 대상으로, users_log에 OLD키워드를 통해 update 되기 전의 tuple을 참조하여 값을 업데이트 해줍니다. 이 외에도 NEW도 있는데, 좀 이후에 살펴보도록 하겠습니다.

 

바로 또 다른 예시를 살펴보도록 하겠습니다. 만약 사용자가 마트에서 상품을 구매할 때마다 지금까지 누적된 구매 비용을 구하는 트리거를 작성해 보도록 하겠습니다.

 

위와같은 상황이라고 해 보겠습니다. 유저 1이 2000 + 5000 을 샀으니까 USER_BUY_STATS에 7000으로 업데이트 해주어야 겠죠?

 

delimiter $$
CREATE TRIGGER sum_buy_prices_trigger
AFTER INSERT
ON buy FOR EACH ROW
BEGIN
    DECLARE total INT;
    DECLARE user_id INT DEFAULT NEW.user_id;
    
    select sum(price) into total from buy where user_id = user_id;
    update user_buy_stats set price_sum = total where user_id = user_id;
END
$$
delimiter ;

이렇게 total 이라는 변수를 선언하고, 누적합을 사용한다음에 user_buy_stats 테이블의 price_sum을 total로 udpate만 해주면 되는 거겠죠?? 이처럼 trigger는 통계하는 용도로도 사용한다는 것 알아두어야 합니다.

 

그리고 그 외에도 trigger는 update, insert, delete 등을 한번에 감지하도록 설정 가능합니다. 참고로 이는 MySQL에서는 불가능하고 PostgreSQL에서는 가능합니다. 하지만 udpate에서 또 주의해야 하는 점이 있는데, FOR EACH ROW를 사용할 때, 만약에 한번에 update 쿼리로 많은 사람의 로우를 변경한다고 하면, 해당 트리거 함수는 조회된 로우의 크기만큼 반복해서 매우 비효율적인 호출을 하게 될 것입니다. 그래서 이러한 반복 호출을 막으려면 FOR EACH STATEMENT를 사용하면 되는데, 이 또한 MySQL에서는 사용 불가능합니다...

 

그리고 아래와 같이 trigger를 통해 더 디테일 한 부분을 조작할 수 있습니다. (postgresql 기준)

CREATE TRIGGER log_user_nickname_trigger
BEFORE UPDATE
ON users
FOR EACH ROW
WHEN (NEW.nickname is DISTINCT FROM OLD.nickname)
EXECUTE FUNCTION log_user_nickname();

이렇게 업데이트 하려는 유저의 닉네임이 이전과 다를시에만 트리거 되게 조건문을 작성해 줄 수도 있습니다. 

 

 

trigger 사용 시 주의 사항

하지만 trigger를 사용하면 소스 코드로는 발견할 수 없는 로직이기 때문에 어떤 동작이 일어나는지 파악하기 어렵고 문제가 생겼을 때 대응하기 어렵다는 단점이 생깁니다. 

 

이처럼 이전에 살펴보았던 logic tier에서는 RDBMS의 procedure코드를 명시적으로 호출해주는 부분이 있었습니다. 하지만 trigger는 가시적이지 않아서 개발도, 관리도, 문제 파악도 힘들어 집니다.

 

그 외에도 trigger가 지들끼리 순환 호출을 해서 호출을 트래킹 하기 힘들다는 단점이 생기게 됩니다. 뿐만 아니라 과도한 트리거 사용은 DB에 부담을 주고 응답을 느리게 만듭니다. 그래서 트리거는 최호의 보루로 남겨놓는 것이 좋습니다!!