[Database] SQL Trigger

SQL trigger 뜻

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

간단한 예제들

예제 1

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

delimiter $$
CREATE TRIGGER log_user_nickname_trigger
	BEFORE UPDATE -- update 이전에 발생하도록 지정
	ON users FOR EACH ROW -- 업데이트 되는 각 row에 대해 실행
	BEGIN
		insert into users_log values(OLD.id, OLD.nickname, now());
	END
	$$
delimiter ;

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

예제 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;
		
		select sum(price) into total from buy where user_id = user_id;
		update user_buy_status set price_sum = total where user_id = user_id;
	END
	$$
delimiter ;

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

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

트리거 여러 이벤트 한번에 감지하기 + row가 아닌 statement 단위로 trigger 실행되도록 설정하기

• update, insert, delete 등을 한번에 감지하도록 설정이 가능하다
• 단, MySQL은 사용 불가능하므로 PostgreSQL을 사용

데이터 변화가 있을 때 임직원의 평균 연봉을 구하는 트리거

CREATE TRIGGER avg_empl_salary_trigger
	AFTER INSERT OR UPDATE OR DELETE
	ON employee
	FOR EACH ROW -- FOR EACH STATEMENT로 변경!
	EXECUTE FUNCTION update_avg_empl_salary();

• FOR EACH ROW 주의할 점

UPDATE employee SET salary = 1.5 * salary WHERE dept_id = 1003;과 같은 쿼리를 날린다면 1003 부서에 임직원이 5명이 있다면 avg_empl_salary_trigger는 5번 실행된다.

하지만, 이는 5번마다 새롭게 계산하는 것은 비효율.. 효율적으로 개선하려면 update가 실행됐을 때 최종적으로 1번만 실행하는 것.

때문에 FOR EACH ROW 대신 FOR EACH STATEMENT로 사용!

trigger를 발생시킬 디테일한 조건을 지정할 수 있다

• MySQL은 불가능

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

• WHEN 절에 조건을 적어서 해당 조건이 참이 될 경우에만 EXECUTE 절을 실행

trigger 사용 시 주의사항

• 소스 코드로는 발견할 수 없는 로직이기 때문에 어떤 동작이 일어나는지 파악하기 어렵고 문제가 생겼을 때 대응하기 어렵다
  • 프로시저는 서버에서 호출하는 코드라도 있는데 트리거는 호출 코드조차 없음.
  • 가시적이지 않아 개발도, 관리도, 문제 파악도 힘들다.
• 과도한 트리거 사용은 트리거끼리 연쇄작용이 일어나 파악하기가 어려워진다
  • DB에 부담을 주고 응답을 느리게 만든다.
  • 디버깅이 어렵다
  • 문서 정리가 특히나 중요하다.

⇒ 트리거는 최후의 카드로 남겨 놓는 것이 어떨까..