Для создания или удаления триггера используйте операторы CREATE TRIGGER или DROP TRIGGER, описанные в разделе 15.1.22, «Оператор CREATE TRIGGER» и разделе 15.1.34, «Оператор 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;

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

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

Возможно определение нескольких триггеров для одной таблицы с одинаковым событием триггера и временем действия. Например, вы можете иметь два 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.

В теле триггера ключевые слова 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. Это также выгодно, если вы хотите выполнить тот же код в нескольких триггерах.

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

См. также раздел 27.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)