Для создания триггера или удаления триггера используйте оператор CREATE TRIGGER или DROP TRIGGER, описанные в Разделе 13.1.20, «Оператор CREATE TRIGGER» и Разделе 13.1.31, «Оператор DROP TRIGGER».

Вот простой пример, который связывает триггер со таблицей для активации при операциях INSERT. Триггер действует как сумматор, суммируя значения, вставленные в один из столбцов таблицы.

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
       FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.01 sec)

Оператор CREATE TRIGGER создает триггер с именем ins_sum, который связан с таблицей account. Он также включает фрагменты, которые указывают время действия триггера, вызывающее событие и действия при активации триггера:

Для использования триггера установите переменную-сумматор в ноль, выполните оператор INSERT, а затем проверьте значение переменной:

mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+

В этом случае значение @sum после выполнения оператора INSERT равно 14.98 + 1937.50 - 100 или 1852.48.

Для удаления триггера используйте оператор DROP TRIGGER. Вы должны указать имя схемы, если триггер не находится в стандартной схеме:

mysql> DROP TRIGGER test.ins_sum;

Если вы удаляете таблицу, любые триггеры для этой таблицы также будут удалены.

Имена триггеров существуют в пространстве имен схемы, что означает, что все триггеры должны иметь уникальные имена в рамках одной схемы. Триггеры в разных схемах могут иметь одинаковые имена.

Начиная с MySQL 5.7.2, можно определить несколько триггеров для одной таблицы с одинаковым вызывающим событием и временем действия. Например, для одной таблицы можно создать два триггера BEFORE UPDATE. По умолчанию триггеры, имеющие одинаковое вызывающее событие и время действия, активируются в порядке их создания. Для изменения порядка активизации триггеров укажите фрагмент после FOR EACH ROW, который указывает FOLLOWS или PRECEDES и имя существующего триггера, имеющего то же вызывающее событие и время действия. С помощью FOLLOWS новый триггер активируется после существующего. С помощью PRECEDES новый триггер активируется до существующего.

Например, следующее определение триггера определяет другой триггер BEFORE INSERT для таблицы account:

mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
       FOR EACH ROW PRECEDES ins_sum
       SET
       @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
       @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
Query OK, 0 rows affected (0.01 sec)

Этот триггер, ins_transaction, похож на ins_sum, но накапливает депозиты и снятия средств отдельно. У него есть фрагмент PRECEDES, который заставляет его активироваться до ins_sum; без этого фрагмента он активировался бы после ins_sum, так как создан после ins_sum.

Перед MySQL 5.7.2 нельзя было создать несколько триггеров для одной таблицы с одинаковым вызывающим событием и временем действия. Например, нельзя создать два триггера BEFORE UPDATE для одной таблицы. Чтобы обойти это ограничение, можно определить триггер, который выполняет несколько операторов, используя конструкцию составного оператора BEGIN ... END после FOR EACH ROW. (Пример приводится позже в этом разделе.)

Внутри тела триггера ключевые слова OLD и NEW позволяют получить доступ к столбцам в строках, затронутых триггером. OLD и NEW — расширения MySQL для триггеров; они не чувствительны к регистру.

В триггере INSERT можно использовать только NEW.col_name; старой строки нет. В триггере DELETE можно использовать только OLD.col_name; новой строки нет. В триггере UPDATE можно использовать OLD.col_name для ссылки на столбцы строки до обновления и NEW.col_name для ссылки на столбцы строки после обновления.

Столбец с именем OLD доступен только для чтения. Вы можете сослаться на него (если у вас есть привилегия SELECT), но не можете его изменять. Вы можете сослаться на столбец с именем NEW, если у вас есть привилегия SELECT на него. В триггере BEFORE вы также можете изменить его значение с помощью SET NEW.col_name = value, если у вас есть привилегия UPDATE на него. Это означает, что вы можете использовать триггер для изменения значений, которые будут вставлены в новую строку или использованы для обновления строки. (Такой оператор SET не имеет эффекта в триггере AFTER, потому что изменение строки уже произошло.)

В триггере BEFORE значение NEW для столбца AUTO_INCREMENT равно 0, а не номер последовательности, который генерируется автоматически при фактической вставке новой строки.

Используя конструкцию BEGIN ... END, вы можете определить триггер, который выполняет несколько операторов. Внутри блока BEGIN вы также можете использовать другие синтаксические конструкции, разрешенные в хранимых процедурах, такие как условные операторы и циклы. Однако, как и для хранимых процедур, если вы используете программу mysql для определения триггера, который выполняет несколько операторов, необходимо переопределить разделитель оператора mysql, чтобы вы могли использовать разделитель оператора ; внутри определения триггера. Следующий пример иллюстрирует эти моменты. Он определяет триггер UPDATE, который проверяет новое значение, которое будет использоваться для обновления каждой строки, и изменяет значение так, чтобы оно находилось в диапазоне от 0 до 100. Это должен быть триггер BEFORE, так как значение необходимо проверить до его использования для обновления строки:

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
       FOR EACH ROW
       BEGIN
           IF NEW.amount < 0 THEN
               SET NEW.amount = 0;
           ELSEIF NEW.amount > 100 THEN
               SET NEW.amount = 100;
           END IF;
       END;//
mysql> delimiter ;

Быстрее определить хранимую процедуру отдельно, а затем вызвать её из триггера с помощью простого оператора CALL. Это также выгодно, если вы хотите выполнить один и тот же код внутри нескольких триггеров.

Существуют ограничения на то, что может появляться в операторах, которые выполняет триггер при активации:

См. также Раздел 23.8, «Ограничения на хранимые программы».

MySQL обрабатывает ошибки при выполнении триггера следующим образом:

Триггеры могут содержать прямые ссылки на таблицы по имени, например, триггер testref, показанный в этом примере:

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  b4 INT DEFAULT 0
);

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW
  BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

delimiter ;

INSERT INTO test3 (a3) VALUES
  (NULL), (NULL), (NULL), (NULL), (NULL),
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Предположим, что вы вставляете следующие значения в таблицу test1, как показано здесь:

mysql> INSERT INTO test1 VALUES 
       (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

В результате четыре таблицы содержат следующие данные:

mysql> SELECT * FROM test1;
+------+
| a1   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4   |
+----+------+
|  1 |    3 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)