[Database] (쉬운 코드) SQL trigger

아래의 내용은 MySQL, PostgreSQL 기준으로 작성한 것입니다. 다른 RDBMS의 SQL 문법은 조금씩 다를 수 있습니다.

trigger 란?

---

데이터베이스에서 어떤 이벤트가 발생했을 떄 자동적으로 실행되는 프로시저(procedure)를 말한다.

다시말해, 데이터에 변경이 생겼을 떄 즉, DB에 insert, update, delete가 발생했을 때 이것이 계기가 되어 자동적으로 실행되는 프로시저를 말한다.

ex. 사용자의 닉네임 변경 이력을 저장하는 트리거를 작성해 보자.

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 ;

위의 트리거는 users 테이블에 update가 발생했을 때, users_log 테이블에 update 되기 전의 tuple을 insert한다.

• OLD
  • update 되기 전의 tuple을 가리킴
  • delete된 tuple을 가리킴

ex. 사용자가 마트에서 상품을 구매할 때마다 지금까지 누적된 구매 비용을 구하는 트리거를 작성해 보자.

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;

위의 트리거는 buy 테이블에 insert가 발생했을 때, user_buy_stats 테이블에 insert된 tuple의 user_id에 대한 누적 구매 비용을 업데이트한다.

• NEW
  • insert된 tuple을 가리킴
  • update된 후의 tuple을 가리킴

trigger를 정의할 때 알고 있으면 좋은 점

1. trigger는 update, insert, delete 등을 한번에 감지하도록 설정이 가능하다.(MySQL은 불가능)

   CREATE TRIGGER avg_empl_salary_trigger
   AFTER INSERT OR UPDATE OR DELETE
   ON employee
   FOR EACH ROW
   EXECUTE FUNCTION update_avg_empl_salary();
   

2. FOR EACH ROW는 튜플을 하나씩 처리하는 것을 의미하기 떄문에 업데이트 문으로 여러 튜플이 영향을 받는 경우에는 비효율적이다. 이런 경우에는 트리거가 한 번만 실행되도록 FOR EACH STATEMENT를 사용할 수 있다(MySQL은 불가능).

   CREATE TRIGGER avg_empl_salary_trigger
   AFTER INSERT OR UPDATE OR DELETE
   ON employee
   FOR EACH STATEMENT
   EXECUTE FUNCTION update_avg_empl_salary();
   

3. trigger를 발생시킬 디테일한 조건을 지정할 수 있다(MySQL은 불가능).

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

trigger 사용 시 주의 사항

---

트리거를 사용할 때 주의해야 할 사항은 다음과 같다.

1. 소스 코드로는 발견할 수 없는 로직이기 때문에 어떤 동작이 일어나는지 파악하기 어렵고 문제가 생겼을 때 대응하기 어렵다.
   • 3-tier architecture에서 data tier에 trigger를 만들어서 사용하면 웹 어플리케이션에서는 trigger 존재를 파악할 수 없다(프로시저는 소스코드에서 존재 유무를 알 수 있음).
   • 가시적이지 않아서 개발도, 관리도, 문제 파악도 힘들어진다.

2. trigger가 연쇄적으로 실행될 수 있어 파악하기가 어렵다.

3. 과도한 트리거 사용은 DB에 부담을 주고 응답을 느리게 만든다.

4. 디버깅이 어렵다.

5. 문서 정리가 특히나 중요하다.

trigger에 대한 [쉬운 코드]의 개인적 생각 : 트리거는 최후의 카드로 남겨 놓는 것이 좋을 것 같다.