Trigger Overview
이름에서 알 수 있듯이, 트리거는 테이블에 이벤트가 발생할 때 실행되는(또는 '트리거'되는) 명령문의 세트입니다.
이벤트
이벤트는 INSERT, UPDATE 또는 DELETE일 수 있습니다. 트리거는 이벤트 시작 전 또는 발생 후에 실행될 수 있습니다. MariaDB 10.2.3까지는 하나의 테이블은 하나의 이벤트/타이밍 조합에 대해 오직 한 가지 트리거만을 정의할 수 있었습니다. 예를 들어, 테이블은 (INSERT 시작 전에 수행되는)BEFORE INSERT 트리거를 단 하나만 가집니다.
LOAD DATA INFILE와 LOAD XML문은 삽입되는 데이터 한 행 마다 INSERT 트리거를 발생시킵니다.
REPLACE문은 아래와 같은 흐름으로 실행됩니다:
- BEFORE INSERT;
- BEFORE DELETE (only if a row is being deleted);
- AFTER DELETE (only if a row is being deleted);
- AFTER INSERT.
INSERT ... ON DUPLICATE KEY UPDATE문은 삽입하려는 행이 이미 존재하는 경우 아래의 흐름으로 실행됩니다:
- BEFORE INSERT;
- BEFORE UPDATE;
- AFTER UPDATE.
그렇지 않은 경우 일반적인 INSERT문과 동일하게 처리됩니다.
TRUNCATE TABLE문은 어떤 트리거도 실행시키지 않습니다.
트리거와 오류
트랜잭션을 지원하지 않는 스토리지 엔진에서는, BEFORE 문에서 오류가 발생하면 해당 구문은 실행되지 않습니다. 트리거가 여러 행에 영향을 미치는 경우에는 현재 행을 삽입하기 전에 실패합니다.
트랜잭션을 지원하는 엔진에서는, 트리거는 해당 트리거를 발생시킨 구문과 동일한 트랜잭션에서 실행됩니다.
SIGNAL 또는 RESIGNAL문과 함께 경고가 발생한다면 ( 즉, SQLSTATE가 '01'로 시작하는 오류가 있는 경우) 이는 오류로 취급됩니다.
트리거 생성
다음은 실제 트리거에 대해 보여주는 간단한 예제입니다. 아래의 두 테이블을 예시로 사용합니다:
CREATE TABLE animals (id mediumint(9) NOT NULL AUTO_INCREMENT, name char(30) NOT NULL, PRIMARY KEY (`id`)); CREATE TABLE animal_count (animals int); INSERT INTO animal_count (animals) VALUES(0);
새 동물이 추가될 때 마다 카운터 값을 증가시키고 싶다고 합시다. 트리거는 아래와 같을 것입니다:
CREATE TRIGGER increment_animal AFTER INSERT ON animals FOR EACH ROW UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
이 트리거는 아래의 요소를 가집니다:
- 이름 (이 경우에는
increment_animal) - 트리거 시점( 이 경우에는 특정 이벤트 이후(after))
- 트리거 이벤트 (
INSERT) - 연관된(associated) 테이블 (
animals) - 실행할 SQL문 (이 경우에는 단순한 UPDATE문)
AFTER INSERT는 이 트리거가 INSERT문 이후(after)에 수행됨을 나타냅니다. 트리거는 이전(before)에 수행되도록 설정될 수도 있고, 트리거를 실행시키는 구문을 AFTER INSERT뿐만 아니라 DELETE 또는 UPDATE로 설정할 수도 있습니다.
이제 우리가 animals테이블에 데이터를 입력할 경우 이 트리거가 실행되어 animal_count 테이블을 증가시킵니다.
SELECT * FROM animal_count;
+---------+
| animals |
+---------+
| 0 |
+---------+
INSERT INTO animals (name) VALUES('aardvark');
INSERT INTO animals (name) VALUES('baboon');
SELECT * FROM animal_count;
+---------+
| animals |
+---------+
| 2 |
+---------+
문법에 대한 자세한 설명은 CREATE TRIGGER를 참조하세요.
트리거 제거
트리거를 제거하기 위해서는 DROP TRIGGER문을 사용하세요. 또한 트리거는 연관된(associated) 테이블이 삭제되는 경우에도 함게 삭제됩니다.
DROP TRIGGER increment_animal;
트리거 메타데이터
Information Schema TRIGGERS 테이블은 트리거에 대한 정보를 저장합니다.
SHOW TRIGGERS문은 유사한 정보를 반환합니다.
SHOW CREATE TRIGGER문은 해당 트리거를 생성한 CREATE TRIGGER문을 반환합니다.
더 복잡한 트리거
트리거는 BEGIN 과 END으로 둘러싸인 여러개의 구문으로 구성될 수 있습니다. 만약 CLI(Command Line Interface)로 작업할 경우 세미콜론을 통해 트리거 내부의 구문을 구분할 수 있도록 임시적으로 새 구분기호(delimiter)를 설정하는 것이 좋습니다. 자세한 내용은 Delimiters in the mysql client를 참조하세요.
DROP TABLE animals;
UPDATE animal_count SET animals=0;
CREATE TABLE animals (id mediumint(9) NOT NULL AUTO_INCREMENT,
name char(30) NOT NULL,
PRIMARY KEY (`id`))
ENGINE=InnoDB;
DELIMITER //
CREATE TRIGGER the_mooses_are_loose
AFTER INSERT ON animals
FOR EACH ROW
BEGIN
IF NEW.name = 'Moose' THEN
UPDATE animal_count SET animal_count.animals = animal_count.animals+100;
ELSE
UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
END IF;
END; //
DELIMITER ;
INSERT INTO animals (name) VALUES('Aardvark');
SELECT * FROM animal_count;
+---------+
| animals |
+---------+
| 1 |
+---------+
INSERT INTO animals (name) VALUES('Moose');
SELECT * FROM animal_count;
+---------+
| animals |
+---------+
| 101 |
+---------+
트리거 오류
트리거에 오류가 있고, 엔진이 트랜잭션을 지원하거나 또는 트리거가 BEFORE 트리거이면, 그 트리거는 실행되지 않으며 동시에 트리거를 발생시킨 원문 SQL문도 실행되지 않도록 막습니다. 엔진이 트랜잭션을 지원하지 않고 트리거가 AFTER 트리거이면, 트리거는 실행되지 않지만 원문 SQL문은 수행됩니다.
여기서는 위의 예제를 삭제하고, 존재하지 않는 필드명을 포함하는 오류가 있는 트리거를 만들어 봅니다. 처음에는 트랜잭션을 지원하는 기본 엔진인InnoDB를 사용하고, 그리고 다시 트랜잭션을 지원하지 않는 MyISAM을 사용해 봅니다.
DROP TABLE animals;
CREATE TABLE animals (id mediumint(9) NOT NULL AUTO_INCREMENT,
name char(30) NOT NULL,
PRIMARY KEY (`id`))
ENGINE=InnoDB;
CREATE TRIGGER increment_animal
AFTER INSERT ON animals
FOR EACH ROW
UPDATE animal_count SET animal_count.id = animal_count_id+1;
INSERT INTO animals (name) VALUES('aardvark');
ERROR 1054 (42S22): Unknown column 'animal_count.id' in 'field list'
SELECT * FROM animals;
Empty set (0.00 sec)
MyISAM 테이블에 대해서 동일한 프로시저를 살펴보면 아래와 같습니다.
DROP TABLE animals;
CREATE TABLE animals (id mediumint(9) NOT NULL AUTO_INCREMENT,
name char(30) NOT NULL,
PRIMARY KEY (`id`))
ENGINE=MyISAM;
CREATE TRIGGER increment_animal
AFTER INSERT ON animals
FOR EACH ROW
UPDATE animal_count SET animal_count.id = animal_count_id+1;
INSERT INTO animals (name) VALUES('aardvark');
ERROR 1054 (42S22): Unknown column 'animal_count.id' in 'field list'
SELECT * FROM animals;
+----+----------+
| id | name |
+----+----------+
| 1 | aardvark |
+----+----------+
다음 예시는 트리거를 사용해 데이터를 검증하는 방법을 보여줍니다. SIGNAL문은 email 필드가 유효한 email이 아닌 경우 의도적으로 오류를 발생시킵니다. 예시가 보여주듯이 새로운 행은 삽입되지 않습니다.(BEFORE 트리거가 실패했기 때문입니다.)
CREATE TABLE user (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name CHAR(20),
last_name CHAR(20),
email CHAR(100)
)
ENGINE = MyISAM;
DELIMITER //
CREATE TRIGGER bi_user
BEFORE INSERT ON user
FOR EACH ROW
BEGIN
IF NEW.email NOT LIKE '_%@_%.__%' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email field is not valid';
END IF;
END; //
DELIMITER ;
INSERT INTO user (first_name, last_name, email) VALUES ('John', 'Doe', 'john_doe.example.net');
ERROR 1644 (45000): Email field is not valid
SELECT * FROM user;
Empty set (0.00 sec)
