Введение в триггеры MySQL

Введение

Цель триггера – дополнительный уровень абстрагирования уровня приложений. Триггер представляет собой именованный объект базы данных, который связан с определенной таблицей, и он будет активизирован, когда для таблицы произойдет одно из событий.

Триггер – это правило, которое помещается в таблицу, и при выполнении DELETE, UPDATE или INSERT совершает дополнительные действия. Например, можно делать запись в журнале об изменении. Но вместо написания двух отдельных запросов (один – для изменения данных, другой для внесения записи в журнал), можно написать триггер, который будет содержать правило: “Когда бы ни изменялась строка, создать новую строку в другой таблице, чтобы сообщить, что были сделаны изменения”. Такой подход создает некоторую избыточность в основном запросе, но теперь нет проходов двух разных пакетов до сервера вашей базы данных, чтобы выполнить два разных действия, что в целом способствует улучшению производительности.

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

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

Если несколько команд в триггере, то через точку с запятой.

Триггер контроля целостности данных

trigger1

Рассмотрим две таблицы sotr(ids,fio) и sotr_in_proekt(idp,ids). Первая таблица содержит идентификатор сотрудника и его имя, второе номера проектов и номера сотрудников, которые принимают участие в этих проектах.

Реализуем триггер, который при удалении данных из таблицы sotr автоматически удаляет данные из таблицы sotr_in_proekt для обеспечения целостности (каскадное удаление).

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

Будем рассматривать триггеры на примере MySQL. Триггеры были введены в MySQL начиная с версии 5.0.2. Выбираем меню Триггер – Новый, после чего укажем:

CREATE TRIGGER exp.before_delete_sotr

BEFORE DELETE ON sotr FOR EACH ROW

BEGIN
DELETE FROM sotr_in_proekt WHERE OLD.ids = ids

END

Разберем пример подробно.

Первая строка

CREATE TRIGGER exp.before_delete_sotr

указывает, что триггер надо создать для базы данных  “exp”, который будет иметь имя “before_delete_sotr”.

Вторая строка

BEFORE DELETE ON sotr FOR EACH ROW

указывает для MySQL, что определенные действия необходимо выполнить перед тем, как провести удаление из данной таблицы

Данные действия определены далее между BEGIN и END:

DELETE FROM sotr_in_proekt WHERE OLD.ids = ids

Переменная OLD определяет переменную, исходные значения которой содержатся в строке перед ее удалением.

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

После создания каждого триггера ОБЯЗАТЕЛЬНО посмотрите, как он работает!

Журналирование и аудит

Создадим триггер, который будет при внесении данных в таблицу sotr будет добавлять введенную фамилию в специально созданную таблицу zh(zapis). Для этого надо указать:

CREATE TRIGGER `after_insert_sotr`

AFTER INSERT ON `sotr` FOR EACH ROW

BEGIN

INSERT INTO zh(zapis) VALUES ( NEW.fio)

END

Здесь используются значения NEW, которые вставляются в таблицу zh.

Реализация бизнес-логики

Добавим таблицу proekt(idp,name,cost,price), содержащую код проекта, название, стоимость. Создадим триггер для UPDATE, который ко вводимой стоимости устанавливает наценку в 25% и помещает новую стоимость в поле price.

Для этого создадим следующий триггер:

CREATE TRIGGER `before_update_cost`

BEFORE UPDATE ON `proekt` FOR EACH ROW

BEGIN

SET New.price = New.cost * 1.25;

END

Замечание. Много где рекомендуют использовать вместо BEFORE AFTER и второй UPDATE после BEGIN, но тогда что-то чудит, т.к. бесконечно вызывается UPDATE.

Теперь определим, чтобы новая стоимость определялась в зависимости от величины старой, а именно если цена меньше 100, то пусть будет 100, если же цена больше 100, но меньше 200, то пусть будет 200.

Для этого используем выражение IF, с учетом которого триггер before_update_cost принимает вид:

CREATE TRIGGER `before_update_cost`

BEFORE UPDATE ON `proekt` FOR EACH ROW

BEGIN

IF NEW.cost < 100 then SET NEW.price = 100; elseIF NEW.cost > 100  AND NEW.cost < 200 then SET NEW.price = 200 ; end if

END

Задания для самостоятельной работы

Поделиться:
  • Добавить ВКонтакте заметку об этой странице
  • Мой Мир
  • Facebook
  • Twitter
  • LiveJournal
  • FriendFeed
  • В закладки Google
  • Google Buzz
  • Яндекс.Закладки
  • StumbleUpon
  • Technorati
  • БобрДобр
  • Memori.ru
  • МоёМесто.ru

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *