참고
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 ;