Лабораторная работа №7
Создание триггеров
Цель работы: изучить операции по созданию триггеров на сервере
MySQL.
Теоретический материал.
Триггер – это специальная хранимая процедура, привязанная к событию изменения содержимого таблицы. Существуют три события изменения таблицы, к которым можно привязать триггер: это изменение содержимого при помощи операторов insert, delete и update.
Триггер создается при помощи оператора create trigger:
CREATE TRIGGER имя_триггера момент_выполнения_триггера событие_триггера
ON имя_таблицы FOR EACH ROW тело_триггера
Таким образом, мы создаем триггер, привязанный к определенной таблице. Нельзя привязать триггер к временной таблице.
Момент выполнения триггера может принимать два значения:
before – действия триггера производятся до выполнения операции изменения таблицы;
after - действия триггера производятся после выполнения операции
изменения таблицы.
Событие триггера показывает, на какое действие должен реагировать
триггер, и может принимать три значения:
insert – триггер привязан к событию вставки новой записи в таблицу;
update – триггер привязан к событию обновления записи таблицы;
delete – триггер привязан к событию удаления записей таблицы.
Тело триггера – это оператор, который необходимо выполнить при возникновении события триггера в таблице. Если требуется выполнить несколько операторов, то следует прибегать к составному оператору
1
begin…end, в котором размещаются все требуемые запросы. Синтаксис и допустимые операторы совпадают с телом хранимых процедур.
Триггеры очень сложно использовать, не имея доступа к новым записям, которые вставляются в таблицу, или старым записям, которые обновляются или удаляются. Для доступа к новым и старым записям используются префиксы new и old соответственно. То есть если в таблице обновляется поле total, то получить доступ к старому значению можно по имени old.total, а к новому – new.total.
Для удаления триггера предназначен оператор drop trigger, который имеет следующий синтаксис:
DROP TRIGGER имя_таблицы.имя_триггера
Получить список триггеров, доступных текущему пользователю,
можно при помощи оператора show triggers, который имеет следующий синтаксис:
SHOW TRIGGERS [FROM имя_БД] [LIKE expr]
Если база данных имеет большое количество триггеров,
результирующую таблицу можно ограничить при помощи ключевого слова like, имеющего тот же синтаксис, что и ключевое слово like в операторе select.
Столбцы из результирующей таблицы оператора show triggers имеют следующий смысл:
Trigger – название триггера;
Event – тип события, который служит сигналом для запуска триггера,
может принимать три значения: insert, update, delete;
Table – название таблицы, к которой привязан триггер;
2
Statement – тело триггера, именно этот блок будет выполняться при запуске триггера;
Timing – момент выполнения триггера: до или после срабатывания оператора операции Event, данный параметр может принимать два значения: before и after;
Created – зарезервированный параметр, в настоящий момент всегда принимающий значение null;
sql_mode – SQL-режим, который действует во время выполнения триггера;
Definer – учетная запись, из-под которой был создан триггер.
Пример:
1.Допустим, у нас есть БД с 2 таблицами: Cities (id, name_city, country_id) и Countries (id, name_country). Создадим триггер,
который при удалении записи из таблицы со странами, будет удалять все города, принадлежащие этой стране из таблицы с
городами.
CREATE TRIGGER delete_cities BEFORE DELETE ON countries FOR EACH ROW
BEGIN
DELETE FROM cities WHERE country_id=OLD.id;
END
2.Допустим, у нас есть БД с таблицей Catalogs (id, name, quantity).
Создадим простейший триггер, который при оформлении нового заказа (добавление новой записи в таблицу catalogs) будет
присваивать значение 1 пользовательской переменной @tot.
CREATE TRIGGER sub_count AFTER INSERT ON catalogs
FOR EACH ROW
BEGIN
SET @tot = 1;
3
END
Исходные данные: БД созданная в лабораторной работе №2.
Порядок выполнения работы.
1.Создать триггер, привязанный к событию insert.
2.Создать триггер, привязанный к событию update.
3.Создать триггер, привязанный к событию delete.
4.Просмотреть список триггеров.
Содержание отчета
1.Цель работы.
2.Схема данных.
3.Скриншот списка триггеров.
4.Листинг созданных триггеров.
5.Скриншоты выполнения триггеров.
6.Выводы о проделанной работе в формате эссе
7.Список использованных источников
Вопросы для самопроверки
1.Что такое триггер?
2.Какие бывают виды триггеров?
3.Как можно просмотреть список триггеров?
4.Что такое префиксы? Как и для чего они используются?
4
Пример выполнения
Цель работы: изучить операции по созданию триггеров на сервере
MySQL.
Ход работы:
Создали таблицы со странами и городами.
Рисунок 1 – Скрипт создания таблиц
Рисунок 2 – Таблица Страны
Рисунок 3 – Таблица города Создали триггеры на удаление, обновление и добавление записей.
Создание триггера на удаление всех данных из таблицы города при удалении страны
Рисунок 4 – Скрипт на удаление
Создание идентичных триггеров на добавление и обновление данных,
подсчитывающих их количество.
Рисунок 4 – Скрипт триггеров
Рисунок 5 – Таблица страны после удаления
Рисунок 6 – Таблица города после удаления страны
Рисунок 7 – Добавили три новые записи
Рисунок 8 – Список созданных триггеров