Для создания или удаления триггера используйте оператор CREATE TRIGGER или DROP TRIGGER, описанные в разделе 15.1.23, «Оператор CREATE TRIGGER» и разделе 15.1.36, «Оператор 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.9, «Ограничения для хранимых программ».

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)