Database 10. Trigger

• MySQL 문법을 기준으로 서술한다.

SQL에서 Trigger란?

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

SQL에서 Trigger 예시1

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

delimiter $$
CREATE TRIGGER log_user_nickname_trigger
BEFORE UPDATE -- update가 발생했을 때 이전에 동작
ON users FOR EACH ROW --users 테이블에서 발생했을 때 업데이트가 되는 각 row에 대해서 동작
BEGIN
  insert into users_log values(OLD.id, OLD.nickname, now()); -- OLD란 update 되기 이전의 tuple 혹은 delete된 tuple을 가리킴
END
$$

delimiter ;

SQL에서 Trigger 예시 2

• 사용자가 마트에서 상품을 구매할 때마다 지금까지 누적된 구매 비용을 구하는 트리거

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; -- insert된 tuple 혹은, update된 후의 tuple을 가리킴

  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 ;

이외에도 trigger를 정의할 때 알고 있으면 좋은 내용

1. update, insert, delete 등을 한번에 감지하도록 설정 가능함(MySQL은 불가능)
2. row 단위가 아니라 statement 단위로 trigger가 실행될 수 있도록 할 수 있다.(MySQL은 불가능)
3. WHEN키워드를 통해 trigger를 발생시킬 디테일한 조건을 지정할 수 있다.(MySQL은 불가능)

• 아래는 postgreSQL의 예시이다.

CREATE Trigger avg_empl_salary_trigger
  AFTER INSERT OR UPDATE OR DELETE
  ON employee
  FOR EACH STATEMENT -- 임직원 5명의 데이터가 변경되어도 trigger는 한번만 시행된다.(EACH의 경우 5번 실행)
  EXECUTE FUNCTION update_avg_empl_salary();

trigger 사용 시 주의 사항

• 소스 코드로는 발견할 수 없는 로직이기 때문에
• 어떤 동작이 일어나는지 파악하기 어렵고 문제가 생겼을 때 대응하기 어렵다.
• 트리거가 연속적으로 발생되면 파악하기 힘들 수 있다.
• DB 과도한 트리거 사용은 DB에 부담을 주고 응답을 느리게 만든다.
• 디버깅이 어렵다.
• 문서 정리가 특히나 중요하다.