Обзор триггеров

Триггер, как следует из названия, — это набор операторов, которые выполняются, или, другими словами, активируются, при возникновении события в таблице.

События

Событием может быть операция INSERT, UPDATE или DELETE. Триггер может выполняться ДО или ПОСЛЕ события. До версии MariaDB 10.2.3, в таблице можно было определить только один триггер для каждой комбинации события/времени: например, в таблице мог быть только один триггер BEFORE INSERT.

Операторы LOAD DATA INFILE и LOAD XML вызывают триггеры INSERT для каждой строки, которая вставляется.

Оператор REPLACE выполняется по следующему сценарию:

• BEFORE INSERT;
• BEFORE DELETE (только если строка удаляется);
• AFTER DELETE (только если строка удаляется);
• 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)
• время срабатывания триггера (в данном случае после указанного события триггера)
• событие триггера (операция INSERT)
• таблица, с которой он связан (animals)
• набор операторов для выполнения (здесь только один оператор UPDATE)

AFTER INSERT указывает, что триггер будет выполняться после 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. Триггеры также удаляются, если удаляется таблица, с которой они связаны.

 DROP TRIGGER increment_animal; 

Метаданные триггеров

В таблице Information Schema TRIGGERS Table хранится информация о триггерах.

Оператор SHOW TRIGGERS возвращает аналогичную информацию.

Оператор SHOW CREATE TRIGGER возвращает оператор CREATE TRIGGER, создающий данный триггер.

Более сложные триггеры

Триггеры могут состоять из нескольких операторов, заключённых в BEGIN и END. Если вы вводите несколько операторов в командной строке, вам потребуется временно установить новый разделитель, чтобы можно было использовать точку с запятой для разграничения операторов внутри вашего триггера. Подробнее см. в Разделители в клиенте mariadb.

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, триггер не будет запущен, и это также предотвратит выполнение исходного оператора. Если движок не транзакционный, а триггер — AFTER, триггер не выполнится, но исходный оператор всё равно будет.

Здесь мы удалим предыдущие примеры и затем снова создадим триггер с ошибкой, полем, которого нет, сначала используя по умолчанию 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 не является корректным адресом электронной почты. Как показано в примере, в этом случае новая строка не вставляется (поскольку это триггер 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)

См. также

• CREATE TRIGGER
• DROP TRIGGER
• Таблица схемы информации TRIGGERS
• SHOW TRIGGERS
• SHOW CREATE TRIGGER
• Ограничения триггеров
• Творческое использование триггеров: Вещи, о которых вы даже не подозревали (видео)