woogiereal-blog

[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 권한이 필요하다.

이외

만약 LOCK TABLES를 실행하여 테이블 락을 걸 경우, 해당 테이블을 대상으로 하는 트리거 또한 락이 걸린다.
25.3.1 Trigger Syntax and Examples

실습

처음 만든 트리거

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 ;

Other articles

[MySQL] EVENT
Published: Thu 28 September 2023
By woogiereal

In MySQL.

tags: MySQL 구문분석 CREATE

참고

MySQL Documentation 13.1.13 CREATE EVENT Statement

prerequisite
- EVENT 권한을 가지고 있어야 한다.
이벤트 생성 구문
```
CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE …
```
read more
[MySQL] FUNCTION
Published: Thu 28 September 2023
By woogiereal

In MySQL.

tags: MySQL 구문분석 CREATE

참고

MySQL Documentation 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements

prerequisite
- CREATE ROUTINE 권한을 가지고 있어야 한다.
추가 설명
- 기본적으로 MySQL은 routine을 만든 생성자에게 ALTER ROUTINE과 EXECUTE 권 …
read more
[MySQL] PROCEDURE
Published: Thu 28 September 2023
By woogiereal

In MySQL.

tags: MySQL 구문분석 CREATE

참고

MySQL Documentation 13.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements

prerequisite
- CREATE ROUTINE 권한을 가지고 있어야 한다.
추가 설명
- 기본적으로 MySQL은 routine을 만든 생성자에게 ALTER ROUTINE과 EXECUTE 권 …
read more