[MySQL] TRIGGER

참고

MySQL Documentation 13.1.22 CREATE TRIGGER Statement

prerequisite

  • TRIGGER 권한을 가지고 있어야 한다.

트리거 란

  • 트리거는 이름을 가진 데이터베이스 객체(named database object)
  • 지정한 테이블에 특정 이벤트가 발생하면 작동 된다.
  • 영구 테이블(permanent table)만 대상으로 삼을 수 있으며 임시 테이블(TEMPORARY table)이나 뷰(view)에는 트리거를 생성할 수 없다.
  • 트리거의 이름들은 schema namespace에 존재하기 때문에 같은 스키마에 동일한 이름의 트리거가 존재할 수 없다(다른 스키마라면 동일한 이름의 트리거를 생성할 수 있다).
  • MySQL 8.0.29 버전 이후로는 IF NOT EXISTS 옵션을 사용하여 동일한 이름의 트리거 생성으로 생기는 에러를 방지 할 수 있다.

트리거 생성 구문

CREATE
    [DEFINER = user]
    TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

trigger_name

  • 같은 스키마 안에서는 동일 트리거명이 존재 할 수 없다.
  • 다른 스키마라면 같은 이름의 트리거가 존재할 수 있다.

trigger_time

  • 트리거가 작동 되는 시기이다.
  • BEFORE 또는 AFTER가 지정될 수 있다.

trigger_event

  • 트리거를 작동 시키는 이벤트
  • 아래의 이벤트들이 지정될 수 있다.
  • INSERT: 새로운 row가 테이블에 인서트 되었을 때(ex. INSERT, LOAD DATA, REPLACE 구문)
  • UPDATE: row가 업데이트 되었을 때
  • DELETE: row가 삭제 되었을 때(ex. DELETE, REPLACE), DROP TABLE, TRUNCATE TABLE, ALTER TABLE DROP PARTITION에는 작동 되지 않는다.
  • trigger_event의 INSERT, UPDATE, DELETE는 table operation을 가르키는 것이지 특정 DML문을 지칭하는 것이 아니다.

trigger_order

  • 해당 테이블에 같은 trigger_event, 같은 trigger_time의 트리거를 생성할 수 있다(ex. BEFORE UPDATE)
  • trigger_order를 지정해 주지 않았다면 만들어진 순서대로 트리거가 실행 된다.
  • 아래의 타입이 지정될 수 있다.
  • FOLLOWS: 새로운 트리거가 가장 나중에 작동 된다.
  • PRECEDES: 새로운 트리거가 지정한 트리거 보다 먼저 실행된다.

trigger_body

  • 트리거 작동 시에 실행되는 구문
  • 복수의 구문을 실행 시키기 위해서는 BEGIN ... END를 사용한다.
  • 몇몇 구문은 trigger에서 허용된지 않는다. Section 25.8, “Restrictions on Stored Programs”
  • 아래 키워드를 사용하여 테이블의 생성, 수정, 삭제 되는 row에 접근할 수 있다.
  • OLD: OLD.col_name 으로 수정되거나 삭제되는 row의 데이터를 참조할 수 있다.
  • NEW: NEW.col_name으로 생성되거나 수정되는 row의 데이터를 참조할 수 있다.
  • 다만 generated column은 OLD, NEW로 참조할 수 없다(ex. AUTO_INCREMENT).Section 13.1.20.8, “CREATE TABLE and Generated Columns”
  • MySQL은 트리거가 생성되었을 때의 sql_mode 시스템 환경변수를 저장한다. 트리거가 작동할 때는 현재 서버의 sql_mode가 아닌 생성 당시의 셋팅으로 실행된다.

DEFINER

  • DEFAUL 값은 CURRENT_USER. CREATE TRIGGER문을 실행하는 유저이다.
  • 트리거가 작동 시의 접근권한을 체크하는데 사용된다(작동시키는 유저가 아니다).
  • 'user_name'@'host_name' 과 같이 지정할 수 있다.
  • 하지만 DEFINER를 지정(specify)하기 위해서는 아래와 규칙이 있다.
  • 만약 계정이 SUPER 권한(SUPER privilege)를 가지고 있지 않다면 다른 계정을 지정할 수 없다.
  • 만약 trigger_body 내에서 OLD.col_name이나 NEW.col_name으로 테이블 row에 대한 참조가 발샐할 경우 해당 테이블에 대한 SELECT 권한이 필요하다.
  • 만약 trigger_body 내에서 SET NEW.col_name = value 같은 구문이 실행 될 경우 해당 테이블에 대한 UPDATE 권한이 필요하다.

이외

실습

처음 만든 트리거

USE md2;

DELIMITER //

CREATE
    DEFINER = 'woogie'@'%'
    TRIGGER IF NOT EXISTS updateUseTime
    AFTER UPDATE # error: Updating of NEW row is not allowed in after trigger
    ON md2.issue FOR EACH ROW
    BEGIN
        IF OLD.issue_state = 'start' AND (NEW.issue_state = 'wait' OR NEW.issue_state = 'complete')
            THEN 
                UPDATE md2.issue # error: generate infinite loop
                SET use_time = use_time + (
                  SELECT ROUND(
                    TIMESTAMPDIFF(
                      minute, 
                      (
                        SELECT MAX(ish1.creation_data) 
                        FROM md2.issue_state_history ish1
                        WHERE ish1.issue_id = NEW.issue_id
                        AND ish1.creation_data NOT IN (
                          SELECT MAX(ish2.creation_data)
                          FROM md2.issue_state_history ish2
                        )
                      ), 
                      (
                        SELECT MAX(ish3.creation_data) 
                        FROM md2.issue_state_history ish3
                        WHERE md2.ish3.issue_id = NEW.issue_id
                      )
                    )/60
                    , 1
                  )
                )
                WHERE issue_id = NEW.issue_id
               ;
        END IF;
    END//

DELIMITER ;

오류 수정 후 트리거

USE md2;

DELIMITER //

CREATE
    DEFINER = 'woogie'@'%'
    TRIGGER IF NOT EXISTS updateUseTime
    BEFORE UPDATE 
    ON md2.issue FOR EACH ROW
    BEGIN
        IF OLD.issue_state = 'start' AND (NEW.issue_state = 'wait' OR NEW.issue_state = 'complete')
            THEN 
                SET NEW.use_time = OLD.use_time + (
                  SELECT ROUND(
                    TIMESTAMPDIFF(
                      minute, 
                      (
                        SELECT MAX(ish1.creation_data) 
                        FROM md2.issue_state_history ish1
                        WHERE ish1.issue_id = NEW.issue_id
                        AND ish1.creation_data NOT IN (
                          SELECT MAX(ish2.creation_data)
                          FROM md2.issue_state_history ish2
                        )
                      ), 
                      (
                        SELECT MAX(ish3.creation_data) 
                        FROM md2.issue_state_history ish3
                        WHERE md2.ish3.issue_id = NEW.issue_id
                      )
                    )/60
                    , 1
                  )
                );
        END IF;
    END//

DELIMITER ;

links

social