13. SQL trigger

trigger 사전적 의미

• 방아쇠
• 계기
• 작동 시키다.

SQL에서 Trigger

• 데이터 베이스에서 어떤 이벤트가 발생했을 때 자동적으로 실행되는 프로시저
• 데이터에 변경이 생겼을 때 즉, insert, update, delete가 발생되었을 때 이것이 계기가 되어 자동적으로 실행되는 프로시저를 의미 한다.

예시 1.

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

mysql> CREATE TRIGGER log_user_nickname_trigger
    -> BEFORE UPDATE
    -> ON employee FOR EACH ROW
    -> BEGIN
    ->  insert into users_log values(OLD.id, OLD.name, now());
    -> END
    -> $$
Query OK, 0 rows affected (0.02 sec)

OLD

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

예제 2.

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

mysql> 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
    -> $$

NEW

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

mysql> select*from user_buy_stats;
+---------+-----------+
| user_id | price_sum |
+---------+-----------+
|       1 |         0 |
+---------+-----------+
1 row in set (0.00 sec)
mysql> insert into buy (id,user_id,price,buy_at) values(1,1,5000,now());
Query OK, 1 row affected (0.00 sec)
mysql> select*from user_buy_stats;
+---------+-----------+
| user_id | price_sum |
+---------+-----------+
|       1 |      5000 |
+---------+-----------+
1 row in set (0.00 sec)

mysql> insert into buy (id,user_id,price,buy_at) values(2,1,15000,now());
Query OK, 1 row affected (0.00 sec)

mysql> select*from buy;
+----+---------+-------+---------------------+
| id | user_id | price | buy_at              |
+----+---------+-------+---------------------+
|  1 |       1 |  5000 | 2023-07-26 00:36:13 |
|  2 |       1 | 15000 | 2023-07-26 00:36:59 |
+----+---------+-------+---------------------+
2 rows in set (0.00 sec)

mysql> select*from user_buy_stats;
+---------+-----------+
| user_id | price_sum |
+---------+-----------+
|       1 |     20000 |
+---------+-----------+
1 row in set (0.00 sec)

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

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

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

UPDATE employee SET salary = 1.5 * salary WHERE dept_id = 1003;
// FOR EACH ROW에 의해서 1003 부서에 임직원이 5명이면 trigger은 다섯번 실행된다.
// 비효율적이기 때문에 FOR EACH STATEMENT로 바꿔서 사용한다.

• row 단위가 아니라 statement 단위로 trigger가 실행될 수 있도록 할 수 있다.(MySQL은 불가능)
• trigger를 실행시킬 수 있는 세부 조건을 설정 할 수 있다.(MySQL은 불가능)

CERATE TRIGGER log_user_nickname_trigger
  BEFORE UPDATE
  ON users
  FOR EACH ROW
  WHEN (NEW.nickname IS DISTINCT FROM OLD.nickname)// nickname가 바뀌었을때 조건을 만족하면 실행
  EXECUTE FUNCTION log_user_nickname();

주의 사항

• 소스 코드로는 발견할 수 없는 로직이기 때문에 어떤 동작이 일어나는지 파악하기 어렵고 문제가 생겼을 때 대응하기 어렵다.
• 프로시저의 경우 비즈니스 로직에서 실행시키는 로직이 있지만 trigger는 가시적이지 않아서 개발도 관리도 문제 파악도 힘들어진다.
• 지나치게 많은 trigger를 사용한다면 데이터 간의 간섭이 생겼을 때 파악하기 힘들다.
• 과도한 trigger 사용은 DB에 부담을 주고 응답을 느리게 한다.
• 디버깅이 어렵다.
• 문서 정리가 특히 중요하다.