[Database] SQL Trigger

2024. 10. 5. 23:10·Database
728x90
반응형

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에 부담을 주고 응답을 느리게 만든다.
    • 디버깅이 어렵다
    • 문서 정리가 특히나 중요하다.

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

728x90
반응형

'Database' 카테고리의 다른 글

[Database] Transaction, Concurrency Control  (1) 2024.10.06
[Database] 테이블 설계 / FD / DB 정규화(Normalization)  (4) 2024.10.05
[Database] Stored Function & Stored Procedure  (2) 2024.10.05
[Database] SQL 고급  (1) 2024.10.05
[Database] SQL 기초  (0) 2024.10.05
'Database' 카테고리의 다른 글
  • [Database] Transaction, Concurrency Control
  • [Database] 테이블 설계 / FD / DB 정규화(Normalization)
  • [Database] Stored Function & Stored Procedure
  • [Database] SQL 고급
mxruhxn
mxruhxn
소소하게 개발 공부 기록하기
    반응형
    250x250
  • mxruhxn
    maruhxn
    mxruhxn
  • 전체
    오늘
    어제
    • 분류 전체보기 (152)
      • Java (21)
      • Spring (6)
      • Database (13)
      • Operating Syste.. (1)
      • Computer Archit.. (0)
      • Network (24)
      • Data Structure (6)
      • Algorithm (11)
      • Data Infra (7)
      • DevOps (12)
      • ETC (27)
      • Project (21)
      • Book (1)
      • Look Back (1)
  • 블로그 메뉴

    • 링크

      • Github
    • 공지사항

    • 인기 글

    • 태그

    • 최근 댓글

    • 최근 글

    • hELLO· Designed By정상우.v4.10.0
    mxruhxn
    [Database] SQL Trigger
    상단으로

    티스토리툴바