Создание триггера

Триггеры

Триггер – это специальная хранимая процедура, привязанная к событию изменения содержимого таблицы. Существуют три события изменения таблицы, к которым можно привязать триггер: это изменение содержимого при помощи операторов insert, delete и update.

Замечание! Поддержка триггеров введена в СУБД MySQL, начиная с версии 5.0.2.

Создание триггера осуществляется при помощи оператора create trigger, который имеет следующий синтаксис:

CREATE TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

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

Конструкция trigger_time указывает момент выполнения триггера и может принимать два значения:

• before – действия триггера производятся до выполнения операции изменения таблицы;
• after - действия триггера производятся после выполнения операции изменения таблицы.

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

• insert – триггер привязан к событию вставки новой записи в таблицу;
• update – триггер привязан к событию обновления записи таблицы;
• delete – триггер привязан к событию удаления записей таблицы.

Замечание! Для таблицы tbl_name может быть создан только одни триггер для каждого из событий trigger_event и момента trigger_time. No есть для каждой из таблиц может быть создано всего шесть триггеров.

Конструкция trigger_stmt представляет тело триггера, т. е. оператора, который необходимо выполнить при возникновении события trigger_event в таблице tbl_name. Если требуется выполнить несколько операторов, то следует прибегать к составному оператору begin…end, в котором размещаются все требуемые запросы.

Синтаксис и допустимые операторы совпадают с тело хранимых процедур. Внутри составного оператора begin…end допускаются все специфичные для хранимых процедур операторы и конструкции:

• другие составные операторы begin…end;
• операторы управления потоком (if, case, while, loop, repeat, leave, iterate);
• объявление локальных переменных при помощи оператора declare и присвоение им значений при помощи оператора set;
• именованные условия и обработчики ошибок.

Замечание! В СУБД MySQL триггеры нельзя привязывать к каскадному обновлению и удалению записей из таблицы типа InnoDB по связи “первичный ключ/внешний ключ”.

Замечание! Для создания триггера при помощи оператора create trigger до версии MySQL 5.1.6 требуется наличие привилегии super, после версии 5.1.6 требуется специальная привилегия trigger.

Триггеры очень сложно использовать, не имея доступа к новым записям, которые вставляются в таблицу, или старым записям, которые обновляются или удаляются. Для доступа к новым и старым записям используются префиксы new и old соответственно. То есть если в таблице обновляется поле total, то получить доступ к старому значению можно по имени old.total, а к новому – new.total.

Создадим простейший триггер, который при оформлении нового заказа (добавление новой записи в таблицу catalogs) будет присваивать значение 1 пользовательской переменной @tot (см. пример ниже).

Как видно из приведенного выше примера, в результате добавления новой записи в таблицу catalogs пользовательской переменной присваивается значение 1. Отредактируем триггер sub_count таким образом, чтобы к пользовательской переменной @tot прибавлялось каждый раз число заказанных товарных позиций number (см. пример ниже).

Замечание! Для корректной работы триггера необходимо, чтобы пользовательская переменная @tot имела значение, отличное от null, т. к. операции сложения с null приводят к null.

Замечание! При создании триггера для таблицы в каталоге данных формируется файл, название которого совпадает с именем таблицы. Файл является текстовым и имеет расширение trg.

Предыдущие два примера демонстрировали работу триггеров после добавления записи в таблицу (after) без вмешательства в запрос. Рассмотрим триггер, который будет включаться до (before) вставки новых записей в таблицу catalogs. Основная задача триггера заключается в ограничении числа заказанных товаров.

Как видно из предыдущего примера, несмотря на то, что заказ оформляется на две товарные позиции, триггер restrict_count изменил значение поля number на 1.

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

Для начала создадим таблицу users, а затем триггер restrict_user для описанной выше задачи.

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

Дата добавления: 2015-05-09