Использование триггеров в СУБД MySQL

Заказать VPS в Германии можно на нашем сайте.

Триггер (англ. trigger) — это хранимая откомпилированная SQL-процедура, которая не вызывается непосредственно, а исполняется при наступлении определенного события внутри базы данных (вставки, удаления, обновления записей). Поддержка триггеров в MySQL началась с версии 5.0.2

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

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

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

Общий вид синтаксиса для создания триггера:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body

Где
trigger_name — название триггера;
trigger_time — время срабатывания триггера. BEFORE — перед событием. AFTER — после события;
trigger_event — событие:

• insert — событие возбуждается операторами insert, data load, replace;
• update — событие возбуждается оператором update;
• delete — событие возбуждается операторами delete, replace. Операторы DROPTABLE и TRUNCATE не активируют выполнение триггера;
• tbl_name — название таблицы;
• trigger_body — выражение, которое выполняется при активации триггера.

Триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм "обновляемого представления".

Пример: создадим две таблицы test и log, напишем триггер, который после добавления каждой записи в 1-ю таблицу будет вести лог этого события:

-- таблица, за которой мы будем следить
CREATE TABLE `test` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`content` TEXT NOT NULL
);
-- лог
CREATE TABLE `log` (
`id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`msg` VARCHAR( 255 ) NOT NULL,
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`row_id` INT( 11 ) NOT NULL
);
-- триггер
DELIMITER |
CREATE TRIGGER `update_test` AFTER INSERT ON `test`
FOR EACH ROW BEGIN
   INSERT INTO log Set msg = 'insert', row_id = NEW.id;
END;

Здесь оператор DELIMITER служит для определения знака начала/окончания процедуры и может состоять более, чем из одного символа (необходимо выбирать разделитель, который не будет использоваться в процедуре).

На столбцы таблицы, к которой привязан триггер можно ссылаться с помощью псевдонимов OLD и NEW. OLD.col_name указывает на столбец с именем col_name до изменения или удаления данных. NEW.col_name относится к колонке новой строке после вставки или существующей - сразу после её обновления.

Для удаления триггера необходимо выполнить запрос:

DROP TRIGGER `update_test`;

Для просмотра триггеров в базе данных используется оператор:

SHOW TRIGGERS;

Триггеры имеют несколько важных особенностей использования:

1. триггеры в MySQL 5 могут создаваться только пользователем с привилегией SUPER;
2. при использовании запроса, затрагивающего N - записей, триггер будет запускаться N - раз;
3. после удаления таблицы, СУБД MySQL автоматически удаляет привязанные к ней триггеры.

Список использованный источников

1. Официальная документация MySQL (http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html)
2. Habrahabr.ru - Триггеры в MySQL (http://habrahabr.ru/blogs/mysql/37693/)
3. Википедия (http://ru.wikipedia.org/wiki/Триггер_(базы_данных)

При полном или частичном использовании любых материалов с сайта вы обязаны явным образом указывать ссылку на handyhost.ru в качестве источника.