Триггеры в MySql

Поддержка триггеров в MySQL включена в 5.0.2 и более старших версиях.

Триггер представляет собой именованный объект базы данных, который связан с таблицей, и он будет активизирован, когда специфическое событие происходит для таблицы. Например, следующие инструкции создают таблицу и вызывают триггер 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.06 sec)

Здесь мы опишем синтаксис для создания и удаления триггеров, покажем некоторые примеры того, как использовать их. Обсуждение ограничений на использование дано в разделе " Ограничения на сохраненные подпрограммы и триггеры".

Синтаксис CREATE TRIGGER

CREATE

[DEFINER = {user | CURRENT_USER}]

TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

Эта инструкция создает новый триггер. CREATE TRIGGER была добавлена в MySQL 5.0.2. В настоящее время использование требует привилегии SUPER.

Триггер становится связанным с таблицей с именем tbl_name, которое должно обратиться к постоянной таблице. Вы не можете связывать триггер с view или таблицей TEMPORARY.

Когда триггер активизирован, предложение DEFINER определяет привилегии, которые применяются, как описано ниже в этом разделе.

• trigger_time - задает время действия. Это может быть BEFORE или AFTER, чтобы задать, что триггер активизируется прежде или после инструкции, которая активизировала это.
• trigger_event - указывает вид инструкции, которая активизирует триггер. Здесь trigger_event может быть одним из следующего:
• INSERT - всякий раз, когда новая строка вставлена в таблицу. Например, через команды INSERT, LOAD DATA или REPLACE.
• UPDATE - всякий раз, когда строка изменяется. Например, через инструкцию UPDATE.
• DELETE - всякий раз, когда строка удалена из таблицы. Например, через инструкции DELETE и REPLACE. Однако, инструкции DROP TABLE и TRUNCATE относительно таблицы НЕ активизируют триггер, потому что они не используют DELETE!
• trigger_stmt задает инструкцию, которая будет выполнена, когда триггер активизируется. Если Вы хотите выполнять много инструкций, используйте операторную конструкцию BEGIN ... END. Это также дает возможность Вам использовать те же самые инструкции, которые являются допустимыми внутри сохраненных подпрограмм.

Важно понять, что trigger_event не представляет литеральный тип инструкции SQL, которая активизирует триггер, поскольку это представляет тип операции таблицы. Например, триггер INSERT активизирован не только инструкцией INSERT, но и LOAD DATA, потому что обе инструкции вставляют строки в таблицу.

Не может быть двух триггеров для данной таблицы, которые имеют те же самые время действия и событие. Например, Вы не можете иметь два триггера BEFORE UPDATE для таблицы. Но Вы можете иметь BEFORE UPDATE и BEFORE INSERT или BEFORE UPDATE и AFTER UPDATE.

Замечание: в настоящее время триггеры не активизированы каскадными действиями внешнего ключа. Это ограничение будет сниматься как можно скорее.

Обратите внимание: до MySQL 5.0.10 триггер не может содержать прямые ссылки к именам таблиц. С MySQL 5.0.10, Вы можете записывать имена, как показано в этом примере:

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)

Вы можете обратиться к столбцам в подчиненной таблице (таблице, связанной с вызывающей), используя псевдонимы OLD и NEW. OLD.col_name обращается к столбцу существующей строки прежде, чем она модифицируется или удалится. NEW.col_name обращается к столбцу новой строки, которая будет вставлена, или же к существующей строке после того, как она модифицируется.

Предложение DEFINER определяет логин MySQL, который нужно использовать при проверке привилегий доступа в вызове триггера. Это было добавлено в MySQL 5.0.17. Если дано значение user, это должно быть логином MySQL в формате 'user_name'@'host_name' (как в команде GRANT). Требуются переменные user_name и host_name. CURRENT_USER также может быть дан как CURRENT_USER(). Заданное по умолчанию значение DEFINER: пользователь, который выполняет инструкцию CREATE TRIGGER. Это также, как DEFINER = CURRENT_USER.

Если Вы определяете предложение DEFINER, Вы не можете устанавливать значение к любому логину, кроме Вашего собственного, если Вы не имеете привилегию SUPER. Эти правила определяют допустимые значения пользователя в DEFINER:

Если Вы не имеете привилегии SUPER, единственное допустимое значение user: Ваш собственный логин, определенный буквально или используя CURRENT_USER. Вы не можете устанавливать DEFINER к некоторому другому логину.

Если Вы имеете привилегию SUPER, Вы можете определять любое синтаксически допустимое имя пользователя. Если такого логина фактически не существует, будет сгенерировано предупреждение.

Хотя возможно создать триггер с несуществующим значением DEFINER, делать этого не следует, поскольку триггер не будет активизирован, пока DEFINER фактически не существует. Иначе, поведение относительно проверки привилегии неопределенно.

Обратите внимание: так как MySQL в настоящее время требует, чтобы была привилегия SUPER для использования CREATE TRIGGER, только второе из предшествующих правил применяется. MySQL 5.1.6 вводит право TRIGGER и требует, чтобы эта привилегия наличествовала для создания триггера, так что с этой версии оба правила работают, а SUPER требуется только для определения значения DEFINER другого, чем Ваш собственный логин.

Начиная с MySQL 5.0.17, MySQL проверяет привилегии триггера подобно этому:

• В момент вызова CREATE TRIGGER пользователь, который выдает инструкцию, должен иметь привилегию SUPER.
• При срабатывании триггера привилегии проверены на соответствие DEFINER. Пользователь должен иметь эти привилегии:

• SUPER.
• SELECT для подчиненной таблицы, если ссылки к столбцам таблицы происходят через OLD.col_name или or NEW.col_name в определении триггера.
• UPDATE для подчиненной таблицы, если столбцы таблицы являются адресатами SET NEW.col_name = value, назначенными в определении триггера.
• Любые другие привилегии обычно требуются для инструкций, выполненных триггером.

До MySQL 5.0.17, MySQL проверяет привилегии подобно этому:

• При вызове CREATE TRIGGER пользователь, который выдает инструкцию, должен иметь привилегию SUPER.
• В момент активации триггера привилегии проверены для пользователя, чьи действия заставили триггер сработать. Этот пользователь должен иметь любые привилегии, которые требуются для инструкций, выполняемых триггером.

Обратите внимание, что введение предложения DEFINER меняет значение CURRENT_USER() в определении триггера: функция CURRENT_USER() работает со значением DEFINER в MySQL 5.0.17 (и выше) или с тем пользователем, чьи действия заставили выполниться триггер (до MySQL 5.0.17).

Синтаксис DROP TRIGGER

DROP TRIGGER [schema_name.]trigger_name

Эта команда уничтожает триггер. Имя базы данных опционально. Если оно не задано, триггер удаляется из заданной по умолчанию базы данных, Вызов DROP TRIGGER был добавлен в MySQL 5.0.2. Использование требует привилегии SUPER.

Обратите внимание: До MySQL 5.0.10, имя таблицы требовалось вместо имени схемы (table_name.trigger_name ). При обновлении с MySQL 5.0 до MySQL 5.0.10 или выше, Вы должны удалить все триггеры перед обновлением и вновь создать их впоследствии, иначе вызов DROP TRIGGER не работает после обновления.

Кроме того, триггеры, созданные в MySQL 5.0.16 или выше, не могут быть удалены в MySQL 5.0.15 или ниже. Если Вы желаете выполнить такой возврат, Вы также должны в этом случае удалить все триггеры и заново их пересоздать после смены версий.

Использование триггеров

Поддержка триггеров включена, начиная с MySQL 5.0.2. Этот раздел обсуждает, как использовать триггеры и некоторые их ограничений.

Триггер является именованным объектом базы данных, который связан с таблицей, и активизируется, когда специфическое событие происходит для этой таблицы. Это очень здорово, когда Вы должны выполнить проверки значений, которые будут вставлены в таблицу или выполнять вычисления на значениях, включаемых в модификации.

Триггер связан с таблицей и определен, чтобы активизироваться, когда для таблицы выполняется инструкция INSERT, DELETE или UPDATE. Триггер может быть установлен, чтобы активизироваться прежде или после вызова инструкции. Например, Вы можете иметь триггер, срабатывающий перед удалением каждой строки из таблицы, или после каждой модификации уже существующей строки в таблице.

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

Следующие инструкции создают таблицу и триггер для нее:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account

-> FOR EACH ROW SET @sum = @sum + NEW.amount;

Команда CREATE TRIGGER создает триггер ins_sum, который связан с таблицей account. Это также включает предложения, которые определяют время активации, событие вызова, и что делать с активированным триггером дальше:

Ключевое слово BEFORE указывает время срабатывания. В этом случае триггер должен активизировать перед каждой строкой, вставленной в таблицу. Другое допустимое ключевое слово здесь: AFTER.

Ключевое слово INSERT указывает событие, которое активизирует триггер. В этом примере триггер срабатывает от инструкции INSERT. Вы можете также создавать триггеры для инструкций DELETE и UPDATE.

Инструкция FOR EACH ROW определяет, что триггер должен сработать один раз для каждой строки, на которую воздействует инструкция в примере. Собственно триггер представляет собой в данном случае простой SET, который накапливает значения, вставленные в столбец amount. Инструкция обращается к столбцу как NEW.amount, что означает "значение столбца amount, которое будет вставлено в новую строку".

Чтобы использовать триггер, установите переменную сумматора в ноль, выполните инструкцию 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 INSERT или AFTER UPDATE для таблицы. Это редко должно быть значительным ограничением, поскольку запросто можно определить триггер, выполняющий много инструкций с помощью конструкции BEGIN ... END после FOR EACH ROW.

Ключевые слова OLD и NEW дают возможность Вам обратиться к столбцам в строках, на которые воздействует триггер OLD и NEW не чувствительны к регистру. В триггере 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 для этого. Это означает, что Вы можете использовать триггер, чтобы изменить значения, которые будут вставлены в новую строку, или использовать это, чтобы модифицировать строку.

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

OLD и NEW представляют собой MySQL-расширения триггеров.

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

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

Триггер не может использовать инструкцию CALL, чтобы вызвать сохраненные процедуры, которые возвращают данные пользователю или применяют динамический SQL. Процедурам позволяется возвратить данные триггеру через параметры OUT или INOUT.

Триггер не может использовать инструкции, которые явно или неявно начинают или заканчивают транзакцию, типа START TRANSACTION, COMMIT или ROLLBACK.

До MySQL 5.0.10 триггер не может содержать прямые ссылки к именам таблиц.

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

Если проблемы с триггером BEFORE, операции на соответствующей строке просто не выполняются.

Триггер BEFORE активизирован попыткой вставить или изменить строку, независимо от того, удачной ли была попытка.

Триггер AFTER выполнен только, если триггер BEFORE и операция со строкой (вместе!) выполняются успешно.

Ошибка в триггере BEFORE или AFTER вызывает сбой всей инструкции, которая вызвала триггер.

Для транзакционных таблиц сбой инструкции должен вызвать обратную перемотку всех изменений, выполняемых инструкцией. Сбой триггера также вызывает обратную перемотку, поскольку является причиной сбоя команды в целом. Для нетранзакционных таблиц такая обратная перемотка не может быть выполнены, так что, хотя операторный сбой произошел, любые изменения, выполненные до ошибки, остаются в силе.

MySQL 5 FAQ по триггерам

1: Имеется ли форум для обсуждения триггеров в MySQL?

Да. http://forums.mysql.com/list.php?99.

2: MySQL 5.1 имеет триггеры операторного уровня или уровня строки?

В MySQL 5.1 все триггеры FOR EACH ROW, то есть триггер активизирован для каждой строки, которая вставлена, модифицируется или удалена. MySQL 5.1 не поддерживает использование триггеров FOR EACH STATEMENT.

3: Имеется ли любое значение по умолчанию для триггеров?

Неявно. MySQL имеет специфическое специальное поведение для некоторых столбцов TIMESTAMP, а также для столбцов, которые определены, используя AUTO_INCREMENT.

4: Как управлять триггерами в MySQL?

В MySQL 5.1 триггер может быть создан, используя инструкцию CREATE TRIGGER, а удален инструкцией DROP TRIGGER.

Информация относительно триггеров может быть получена, запрашивая таблицу INFORMATION_SCHEMA.TRIGGERS.

5: Имеется ли способ просмотреть все триггеры в конкретной базе данных?

Да. Вы можете получать распечатку всех триггеров, определенных в базе данных dbname, запросом к таблице INFORMATION_SCHEMA.TRIGGERS примерно так:

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE,

ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS

WHERE TRIGGER_SCHEMA='dbname';

Вы можете также использовать инструкцию SHOW TRIGGERS, которая является специфической для MySQL.

6: Где хранятся триггеры?

Триггеры в настоящее время сохранены в .TRG-файлах, один такой файл на таблицу. Другими словами, триггер принадлежит таблице.

В будущем, мы планируем изменять это так, чтобы информация триггера включалась в .FRM-файл, который определяет структуру таблицы. Также планируется сделать триггеры уровня базы данных, чтобы привести их в согласие с SQL-стандартом.

7: Может триггер вызывать сохраненную процедуру?

Да.

8: Может триггер обращаться к таблицам?

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

9: Может триггер вызывать внешнюю прикладную программу через UDF?

Нет, не в настоящее время.

10: Может триггер модифицировать таблицы на удаленном сервере?

Да. Таблица на удаленном сервере могла бы модифицироваться, используя тип памяти FEDERATED.