Очень может быть, что вы знаете, что такое триггер базы данных, хотя бы в общих терминах. Есть даже шанс, что вы знаете, что MySQL поддерживает триггеры и имеет практику работы с ними.
Но скорее всего, что большинство из вас, даже вооруженные знаниями не представляют себе, какие преимущества скрывают триггеры MySQL.
Этот инструмент должен быть у вас на вооружении, так как триггеры могут полностью изменить ваш способ работы с данными.
Введение: что такое триггер
“Не смотря на то, что приложения становятся все более и более сложными, мы можем абстрагировать уровень приложений для того, чтобы управлять ими и увеличивать удобство процесса разработки.”
Для тех, кто не знает, триггер — это правило, которое помещается вами в таблицу, и при выполнении DELETE, UPDATE или INSERT совершает дополнительные действия. Например, мы можем делать запись в журнале об изменении.
Но вместо написания двух отдельных запросов (один — для изменения данных, другой для внесения записи в журнал), можно написать триггер, который будет содержать правило: “Когда бы ни изменялась строка, создать новую строку в другой таблице, чтобы сообщить, что были сделаны изменения”.
Такой подход создает некоторую избыточность в основном запросе, но теперь нет проходов двух разных пакетов до сервера вашей базы данных, чтобы выполнить два разных действия, что в целом способствует улучшению производительности.
Триггеры были введены в MySQL начиная с версии 5.0.2. Синтаксис триггеров несколько чужероден. MySQL использует стандарт ANSI SQL:2003 для процедур и других функций.
Если вы работаете с языками программирования, то понять его будет не сложно. Спецификация отсутствует в свободном доступе, поэтому мы постараемся использовать простые структуры и будем объяснять, что происходит в триггере.
Будут использоваться такие же структуры, как и в любом языке программирования.
Как уже упоминалось выше, триггеры выполняются как процедуры при событиях UPDATE, DELETE и INSERT. Они могут быть выполнены либо до либо после определения события.
Таким образом Вы можете определить триггер, которые будет выполняться перед DELETE или после DELETE, и так далее.
Это значит, что можно иметь один триггер, который выполнится до INSERT и совершенно другой, который выполнится после INSERT, что является весьма мощным инструментом.
Начало: структура таблиц, инструменты и заметки
В статье мы будем работать с выдуманной системой для корзины покупок, каждый элемент которой будет иметь цену. Структура данных будет проста, насколько это возможно с целью продемонстрировать процедуры работы с триггерами.
Наименования таблиц и столбцов придуманы с целью облегчения понимания, а не для реальной работы. Также используется TIMESTAMPS для облегчения учебного процесса. Таблицы имеют имена carts, cart_items, cart_log, items, items_cost.
Также будут использоваться очень простые запросы. Нет связи между переменными и не используется никакого ввода данных. Запросы подготавливались так, чтобы быть как можно более простыми и понятными для чтения.
Для определения времени выполнения использовался Particle Tree PHP Quick Profiler. Для иллюстрации эффектов на базе данных использовался Chive.
Chive предназначен только для MySQL 5+ и очень похож на PHPMyAdmin. Он имеет более выразительный интерфейс, но содержит значительно больше ошибок на текущий момент.
Использование Chive обусловлено желанием представить более выразительные скрин шоты запросов.
Вам также может понадобиться поменять разделитель MySQL при создании триггеров. Оригинальный разделитель MySQL — это ; , но так как мы будем использовать разделитель для добавленных запросов, то может понадобиться явно указать разделитель, чтобы создавать запросы из командной линии. При использование Chive нет необходимости менять разделитель.
Чтобы изменить разделитель, нужно выполнить команду перед командой триггера:
DELIMITER $$
А после команды триггера надо ввести:
DELIMITER ;
Простой триггер: целостность данных
Если Вы захотите выполнить даже незначительную нормализацию структуры базы данных, может получиться так, что нужно будет удалять источник основных данных, который имеет фрагменты, участвующие в общем потоке данных. Например, у вас может быть cart_id, который ссылается на две или три таблицы без внешних ключей, особенно при использовании механизма MyISAM, который их не поддерживает.
Для такого случая раньше вы возможно выполняли следующие операции:
$sql = 'DELETE FROM no_trigger_cart_items WHERE cart_id = 1';$rs = $this->db->query($sql);$sql = 'DELETE FROM no_trigger_carts WHERE cart_id = 1';$rs = $this->db->query($sql);
Теперь, в зависимости от того, насколько вы сами организованы, у вас может быть одна API или метод, который очищает ваши корзины.
Если это ваш случай, то у вас будет изолированная функция, которая выполняет два запроса.
Если самоорганизация — не ваш конек, то Вам придется всегда помнить, что нужно очищать элементы корзины, когда вы удаляете определенную корзину. Не сложно, но если Вы забудете, то потеряете целостность данных.
Вернемся к триггерам. Создадим простой триггер, который при удалении корзины будет удалять все элементы корзины, которые имеют такой же cart_id:
CREATE TRIGGER `tutorial`.`before_delete_carts`BEFORE DELETE ON `trigger_carts` FOR EACH ROWBEGINDELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;END
Очень простой синтаксис. Давайте разберем триггер подробно.
Первая строка“CREATE TRIGGER `tutorial`.`before_delete_carts`”. Это команда для MySQL создать триггер для базы данных “tutorial”, который будет иметь имя “before_delete_carts”. Будем использовать схему имен для триггеров “Когда_Что_Таблица”.
Вторая строка указывает для MySQL определение триггера “BEFORE DELETE ON `trigger_carts` FOR EACH ROW”. Мы говорим MySQL, что перед тем, как провести удаление из данной таблицы, для каждой строки нужно сделать что-то. Что нужно сделать, объясняется далее между BEGIN и END.
“DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;” Для MySQL задается, что перед тем, как удалить из trigger_carts, нужно взять OLD.cart_id и также удалить из trigger_cart_items. Синтаксис OLD определяет переменную.
Он будет обсуждаться в следующем разделе, где будут комбинироваться OLD и NEW.
Преимущество использования триггера — целостность ваших данных перемещается с уровня логики на уровень данных, где она и должна быть. Также есть и некоторый прирост производительности системы.
Два запроса:
Один запрос с триггером:
Как вы можете видеть, существует небольшой прирост производительности, которого следовало ожидать. База данных для примера использует тот же самый сервер, что и клиент.
Но если сервер баз данных расположен в другом месте, то следует ожидать более значительной разницы, так как ко времени выполнения запросов добавится время передачи данных между серверами.
Также нужно отметить, что первый раз триггер может выполняться значительно медленнее, чем в следующие разы.
Перемещение логики данных на уровень данных подобно тому, как задание стиля перемещается с уровня разметки на уровень презентации, что известно всему миру как CSS.
Чудесный простой триггер: журналирование и аудит
Следующий пример, который мы рассмотрим связан с журналированием событий. Например, мы хотим наблюдать за каждым товаром, который помещается в корзину. Возможно, мы хотим отслеживать рейтинг покупки товаров.
Возможно, мы просто хотим иметь копию каждого товара, помещенного в корзину, не обязательно для продажи, а для анализа поведения покупателей.
Какими бы ни были причины, давайте посмотрим на триггер INSERT, который открывает возможности для журналирования или аудита наших данных.
До использования триггера, вероятно мы делали что-то похожее:
Теперь мы можем создать очень простой триггер для процесса журналирования:
CREATE TRIGGER `after_insert_cart_items`AFTER INSERT ON `trigger_cart_items` FOR EACH ROWBEGININSERT INTO trigger_cart_log (cart_id, item_id)VALUES (NEW.cart_id, NEW.item_id);END
Первая строка “CREATE TRIGGER `after_insert_cart_items`”. Для MySQL задается команда, создать триггер с именем “after_insert_cart_items”.
Имя может быть “Foo”, или “BullWinkle” или какое-то другое, но лучше использовать ранее описанную схему имен триггера. Далее следует “AFTER INSERT ON `trigger_cart_items` FOR EACH ROW”.
Снова мы говорим, что после того, как что-то будет вставлено в trigger_cart_items, для каждой строки нужно выполнить операции между BEGIN и END.
Строка “INSERT INTO trigger_cart_log (cart_id, item_id) VALUES (NEW.cart_id, NEW.item_id);” является стандартным запросом с использованием двух переменных. Здесь используются значения NEW, которые вставляются в таблицу cart_items.
Снова выполнение нашего запроса осуществляется быстрее:
Для проверки, что триггер работает, посмотрим значения в таблице:
Более сложный триггер: бизнес логика
Начиная с этого момента мы перестанем рассматривать старый способ использования множественных запросов и их сравнение с техникой использования триггеров. Давайте рассмотрим несколько более продвинутых примеров использования триггеров.
Бизнес логика — это место, где плодятся ошибки. Не смотря на осторожность и внимание к организации процесса, всегда что-то идет не так. Триггер для UPDATE позволяет несколько смягчить такое положение.
У нас есть возможность в триггере вычислить значение OLD и установить значение NEW на основе оценки. Например, мы хотим всегда устанавливать цену на товар с 30% надбавкой к стоимости.
Это приводит к тому, что когда мы изменяем (UPDATE) стоимость, мы должны изменить (UPDATE) цену. Давайте используем триггер.
CREATE TRIGGER `after_update_cost`AFTER UPDATE ON `trigger_items_cost` FOR EACH ROWBEGINUPDATE trigger_itemsSET price = (NEW.cost * 1.3)WHERE item_id = NEW.item_id;END
Мы изменяем таблицу товаров с ценами, основанными на NEW.cost * 1.3. Если ввести стоимость $50, то цена должна быть $65.
Данный триггер работает отлично.
Давайте рассмотрим более сложный пример. У нас уже есть правило, которое изменяет цену товара на основе стоимости. Теперь мы хотим установить некоторую ярусность в ценах. Если цена меньше $50, то актуальное значение будет $50. Если цена больше $50, но меньше $100, то актуальное значение будет $100.
Для того, чтобы решить задачу, мы снова будем работать с UPDATE, но в этот раз триггер будет выполняться до выполнения запроса. Также будет использоваться выражение IF.
Вот текст триггера:
CREATE TRIGGER `before_update_cost`BEFORE UPDATE ON `trigger_items_cost` FOR EACH ROWBEGINIF NEW.cost < 50 THENSET NEW.cost = 50;ELSEIF NEW.cost > 50 AND NEW.cost < 100 THENSET NEW.cost = 100;END IF;END
Это не запрос, а перекрытие значений. Если цена меньше $50, то устанавливаем ее $50. Если цена лежит между $50 и $100, то устанавливаем ее $100. Если она выше, то просто оставляем ее такой, какая она есть. Синтаксис не отличается от других серверных языков. Нужно закрыть выражение IF с помощью END IF.
Проверим работу нашего триггера. Если ввести значение стоимости $30, то цена должна быть $50:
Для значения стоимости $85:
Для проверки того, что триггер AFTER UPDATE все еще работает, цена должна быть $130:
Заключение
В данной статье мы только слегка задели айсберг триггеров в MySQL. Они позволяют переносить правила работы с данными с уровня логики приложения на уровень данных. Может быть, использование триггеров на одностраничном сайте и является суетой, которая отнимает время и силы. Но сложные вэб приложения могут совершенно преобразиться при использовании триггеров.
Источник: https://ruseller.com/lessons.php?id=630
Создание триггеров в MySQL
Введение
СУБД MySQL является превосходным сервером БД. Основная причина её популярности – это быстрота (производительность) и стабильность. Система MySQL хорошо проверена и надёжна.
MySQL является прекрасным инструментом изучения удаленных баз данных, благодаря простоте использования, а также исключительно скромными требованиями к дисковому пространству и памяти.
В MySQL используется двойная схема лицензирования: бесплатная и коммерческая.
Пользовательский интерфейс представляет собой «интерфейс командной строки», который называется «монитор MySQL». При установке MySQL по умолчанию устанавливается и Монитор MySQL.
Операторы SQL
Язык SQL (structured query language – язык структурированных запросов) разработан фирмой IBM в 1974 году и был принят Американским Национальным институтом стандартов (ANSI) в качестве национального стандарта США в 1992 году. Операторы SQL поддерживаются большинством СУБД. Язык SQL ориентирован на текст. Т.к. он был разработан задолго до появления графических интерфейсов пользователя, то для работы с ним требуется лишь текстовый редактор.
Этот язык представляет собой настолько удачную программную разработку для манипулирования данными реляционной модели, что в настоящее время он является наиболее распространенным программным продуктом такого рода.
Правила:
-
Ключевые слова SQL не зависят от регистра
-
каждая команда и каждый оператор MySql заканчивается символом «точка с запятой»
-
[] не обязательный элемент
Триггеры
Поддержка триггеров появилась в MySQL начиная с версии 5.0.2.
Триггер – именованный объект БД, который ассоциирован с таблицей и активируемый при наступлении определенного события, связанного с этой таблицей.
Триггер – это процедура, которая автоматически начинает работает при наступлении определённого события: добавления новой записи в таблицу базы данных, редактирования записи или удаления записи из таблицы БД.
В MySQL для каждой таблицы базы данных можно одновременно хранить на сервере максимум шесть триггеров. Для каждого события для одной таблицы БД можно сохранить два триггера с учётом времени их срабатывания: «до наступления события» и «после наступления события».
- Общий вид оператора создания триггера
- CREATE TRIGGER имя_триггера время_триггера событие_срабатывания_триггера ON имя_таблицы FOR EACH ROW выражение_выполняемое_при_срабатывании_триггера ;
- ВРЕМЯ_ТРИГГЕРА
Определяет время свершения действия триггера. BEFORE означает, что триггер выполнится до завершения события срабатывания триггера, а AFTER означает, что после.
- СОБЫТИЕ_СРАБАТЫВАНИЯ_ТРИГГЕРА
- INSERT при операциях вставки ( INSERT, LOAD DATA )
- UPDATE при изменении записи
- DELETE при удалении записи
В теле триггера при обращении к полю таблицы БД перед именем поля необходимо указывать New или Old. Если данные для таблицы новые, то New, если данные уже были в таблице, то Old.
Пример 1:
CREATE TRiGGER primer1 BEFORE iNSERT ON SPORT FOR EACH ROW
SET New. VES_Tochno = New.VES – 1 ;
- В таблице SPORT имеются поля VES и VES_Tochno.
- Во время добавления новой записи к таблице SPORT, пользователь в поле VES вводит данные, а значение для поля VES_Tochno вводится NULL.
- Но при этом поле VES_Tochno заполняется автоматически с учётом алгоритма записанного в триггере.
- Пример 2:
- CREATE TRiGGER primer2 AFTER iNSERT ON SPORT FOR EACH ROW
INSERT INTO ARHiV VALUES (NULL, New.Nom, New.FiO) ;
- В базе данных существует таблица ARHiV c тремя полями: автоинкрементное ключевое, для номера спортсмена и для ФИО спортсмена.
- Во время добавления новой записи к таблице SPORT, автоматически добавляется новая запись к таблице ARHiV, и значения полей Nom и Fio записываются не только в таблицу SPORT, но и в таблицу ARHiV.
- Пример 3:
- В базе данных существует таблица ARHiV_1 c тремя полями: автоинкрементное ключевое, для номера спортсмена и для ФИО спортсмена.
- Создадим двумя способами триггер, который во время удаления записи из таблицы SPORT, автоматически будет добавлять эту запись к таблице ARHiV_1 и значения полей Nom и Fio удаляемой из таблицы SPORT записи перенесутся в таблицу ARHiV_1.
- Способ первый
- Mysql DELIMITER #
- Mysql CREATE TRiGGER primer3 BEFORE DELETE ON SPORT FOR EACH ROW
- BEGIN
- SET @ A = OLD.Fio ;
- SET @ B = OLD.Nom ;
- INSERT INTO ARHiV_1 VALUES (NULL, @ B, @ A) ;
- END #
- Mysql DELIMITER ;
- Способ второй
- CREATE TRiGGER primer3 BEFORE DELETE ON SPORT FOR EACH ROW
INSERT INTO ARHiV_1 VALUES (NULL, OLD.Nom, OLD.Fio) ;
- Удаление триггера
- DROP TRIGGER имя_триггера ;
- Триггеры можно использовать для проверки корректности ввода данных.
- В MySql Workbench при создании структуры таблицы базы данных, на закладке «Triggers» надо выбрать одно из шести возможных событий и в теле процедуры-триггера прописать необходимые действия.
-
- Лабораторная работа
- Тема: Создание триггеров в MySQL
- Цель: Обрести практические навыки создания триггеров в СУБД MySQL
- Задания лабораторной работы:
-
Установите связь с сервером
-
Создайте на сервере свою базу данных с именем, соответствующим Вашей фамилии (например, BDKomarova)
-
Создайте таблицу Student_ВАША ФАМИЛИЯ (например, Student_Komarova)содержащую поля: N_stud (автоинкрементное), FIO(символьное), D_R (дата), Gruppa(символьное), Stipendiya (вещественное) , Adres(символьное)
-
Просмотрите структуру таблицы
-
Заполните таблицу Student_ВАША ФАМИЛИЯ следующими записями (10 студентов)
| N_Stud | FIO | D_R | Gruppa | Stipendiya | Kod_spec |
| 1 | Сидоров | 1992-07-23 | 4п1 | 880 | 3322 |
| 2 | Петров | 1993-11-07 | 4п3 | NULL | 4466 |
| 3 | Григорьев | 1992-04-19 | 4п2 | 920 | 8811 |
| 4 | Данилова | 1992-06-11 | 4п2 | 1200 | 8811 |
| 5 | Андреев | 1993-11-06 | 4п1 | NULL | 3322 |
| 6 | Серова | 1991-02-25 | 4п1 | NULL | 3322 |
| 7 | Коробкова | 1992-11-20 | 4п2 | 880 | 8811 |
| 8 | Смирнов | 1991-07-16 | 4п2 | 920 | 8811 |
| 9 | Подгорнова | 1991-09-25 | 4п3 | 1200 | 4466 |
| 10 | Лебедев | 1991-07-26 | 4п3 | 920 | 4466 |
-
Проверьте заполнение таблицы. Для этого составьте запрос для вывода на экран всей информации о студентах
-
Измените структуру таблицы Student, добавьте в неё поле Stip_New (новая стипендия)
-
Создайте триггер TR1 на добавление новой записи в таблицу Student, который рассчитывает новую стипендию студента, увеличивая базовый размер стипендии в 2 раза.
-
Проверьте работу триггера. Для этого добавьте в таблицу Student две записи
-
Составьте запрос для вывода на экран всей информации о студентах
-
Измените структуру таблицы Student, добавьте в неё поле PREMiYA
-
Назначьте каждому студенту премию в размере 500 рублей
-
Создайте триггер TR2 на изменение записи в таблице Student, который рассчитывает премию студента, как сумму размера базовой стипендии и 1000 рублей.
-
Проверьте работу триггера. Для этого измените размер стипендии студента АНДРЕЕВ на 1200
-
Измените размер стипендии студентки СЕРОВА на 900
-
Cоставьте запрос для вывода на экран всей информации о студентах
-
Создайте новую таблицу базы данных ARHiV_1, которая содержит следующие поля: номер записи (целочисленное, ключевое, автоинкрементное), номер студента, фамилия студента, сумма стипендии и премии
-
Создайте триггер TR3 на удаление записи из таблицы Student, который при удалении записи из таблицы Student записывает в таблицу ARHiV_1 информацию: номер студента, фамилия студента, сумма полей Stipendiya и Premiya
-
Проверьте работу триггера. Для этого удалите из таблицы Student информацию о студентах АНДРЕЕВ, СЕРОВА
-
Cоставьте запрос для вывода на экран всей информации о студентах
-
Cоставьте запрос для вывода на экран всей информации из таблицы ARHiV_1
-
Просмотрите все имеющиеся в базе данных триггеры
-
Удалите все созданные триггеры TR1 .. TR3
-
Создайте новую таблицу базы данных ARHiV_2, которая содержит следующие поля: номер записи (целочисленное, ключевое, автоинкрементное), фамилия студента, группа и размер стипендии
-
Создайте триггер TR4 на добавление новой записи в таблицу Student, который записывает фамилию студента, его группу и увеличенную стипендию студента в таблицу ARHiV_2.
-
Проверьте работу триггера. Для этого добавьте в таблицу Student две записи о студентах Вашей учебной группы
-
Cоставьте запрос для вывода на экран всей информации о студентах
-
Cоставьте запрос для вывода на экран всей информации из таблицы ARHiV_2 и убедитесь в правильной работе триггера.
-
Измените структуру таблицы Student, добавив в неё поле YYY (год рождения)
-
Измените структуру таблицы Student, добавив в неё поле MMM (месяц рождения)
-
Измените структуру таблицы Student, добавив в неё поле DDD (день рождения)
-
Создайте триггер TR5 на добавление новой записи в таблицу Student, который выделяет из даты рождения студента отдельно год, месяц, число и записывает их в соответствующие поля таблицы.
-
Проверьте работу триггера. Для этого добавьте в таблицу Student две записи о студентах Вашей учебной группы
-
Cоставьте запрос для вывода на экран всей информации о студентах и убедитесь в правильной работе триггера
-
Создайте триггер TR6 на редактирование записи в таблице Student, который переводит студента в группу заочного отделения 2ЗАО1.
-
Проверьте работу триггера. Для этого измените двум последним студентам размер премии на Ваше усмотрение
-
Cоставьте запрос для вывода на экран всей информации о студентах и убедитесь в правильной работе триггера.
-
Создайте триггер TR7 на своё усмотрение и продемонстрируйте его работу.
-
Просмотрите все имеющиеся в базе данных триггеры.
-
Удалите триггеры.
-
Оформите отчёт по лабораторной работе
Отчёт по лабораторной работе должен представлять собой файл Отчёт.doc, дополненный рисунками экранных форм с результатами выполнения каждого задания.
Например,
8
Источник: https://multiurok.ru/files/sozdanie-triggerov-v-mysql.html
Как создать триггер в MySQL
Загрузка …
Мужчина, который умно говорит о любви, не очень любит (Ж. Санд).
2 мин для чтения
Добавить в избранное
Загрузка…
1 июня 2017
В этой статье мы расскажем, как создать триггер в MySQL. Триггер в MySQL, это процедурный код, который автоматически выполняется как ответ на определенные события в конкретной таблицы или представления в базе данных. Мы берем реальный пример здесь, если новая запись (New Employee Joining), вставлена в таблицу сотрудников, то она должна быть также вставлена в таблицу заработной платы.
Есть три типа триггеров поддерживаемые в Mysql версии 5.0.2. Они написаны ниже:
Триггер для вставки в Mysql
Здесь приведен пример синтаксиса записи триггера для вставки в Mysql.
CREATE TRIGGER salary_trigger
BEFORE UPDATE ON employee_table
REFERENCING NEW ROW AS n, OLD ROW AS o
FOR EACH ROW
IF n.salary o.salary THEN
END IF;
Пример триггера для вставки в MySQL.
— удалить существующий триггер с именем Andreyextrigger
DROP TRIGGER IF EXISTS `Andreyextrigger`;
DELIMITER $$
CREATE TRIGGER `Andreyextrigger`
AFTER INSERT ON Table_Current
FOR EACH ROW
BEGIN
UPDATE Table_Record
SET `Value` = NEW.`Value`
WHERE `Name` = NEW.`Name`
AND `Value` < NEW.`Value`;
END $$
DELIMITER;
Здесь приведен пример синтаксиса, чтобы написать триггер обновления в Mysql.
ELIMITER //
CREATE TRIGGER Andreyextrigger
AFTER UPDATE
ON contacts FOR EACH ROW
BEGIN
DECLARE emp_name varchar(50);
— Найти имя сотрудника, осуществить вставку в таблицу
SELECT employee() INTO emp_name;
— Вставить запись в таблицу salaries
INSERT INTO salaries
( emp_id,
updated_date,
updated_by)
VALUES
( NEW.emp_id,
SYSDATE(),
emp_name );
END; //
DELIMITER ;
Триггер удаления записи в Mysql
Пример синтаксиса триггера для удаления записи в Mysql
Источник: https://andreyex.ru/bazy-dannyx/baza-dannyx-mysql/kak-sozdat-trigger-v-mysql/
Создание триггеров в MySQL
Вы здесь:
Главная — MySQL — MySQL Основы — Создание триггеров в MySQL
Триггеры — это хранимые процедуры, которые выполняются при наступлении определённого события. Под событием подразумевается отправка определённого запроса. Например, при добавлении записи в таблицу триггер может быть вызван и будет выполнен определённый код.
Чтобы проверить, как работают триггеры, давайте создадим с Вами 2 таблицы. Первая таблица — это страны (countries):
- id — уникальный идентификатор.
- title — название страны.
Таблица с городами (cities):
- id — уникальный идентификатор.
- title — название города.
- country_id — id той страны, которой принадлежит данный город.
Теперь давайте сделаем так, что если вдруг удаляется запись из таблицы со странами, то все города, принадлежащие этой стране, удаляются из таблицы с городами.
Такой триггер создаётся в MySQL следующим образом:
DELIMITER //
CREATE TRIGGER `delete_cities` BEFORE DELETE ON `countries`
FOR EACH ROW BEGIN
DELETE FROM `cities` WHERE `country_id`=OLD.`id`;
END
После создания триггера при удалении страны автоматически удалятся из другой таблицы все города, принадлежащие этой стране. Теперь разберём подробнее синтаксис создания триггера:
- DELIMITER — разделитель, который необходимо указать, иначе на «;» будет выдаваться ошибка.
- `delete_cities` — это просто имя триггера.
- BEFORE DELETE — данная команда означает, что триггер будет срабатывать непосредственно перед удалением записи. Вместо BEFORE может быть AFTER, а вместо DELETE другое событие, например, INSERT или UPDATE.
- `countries` — название таблицы, на которую устанавливается триггер.
- FOR EACH ROW — начало команды, выполняемой при срабатывании триггера.
- BEGIN и END — отдельный блок выполняемой команды.
- OLD.`id` — обращение к полю `id` удаляемой записи из таблицы `countries`.
Сразу скажу, что здесь я показал простой пример. В реальности, код после FOR EACH ROW может быть не 2 строчки, а очень и очень большим. Там так же можно писать циклы, условия, различные сравнения и многое-многое другое.
Вот таким образом создаются триггеры в MySQL.
Предыдущая статья Следующая статья
Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!
Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.
Если Вы не хотите пропустить новые материалы на сайте,то Вы можете подписаться на обновления: Подписаться на обновления
Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.
Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):
-
Кнопка:
Она выглядит вот так:
-
Текстовая ссылка:Как создать свой сайт
Она выглядит вот так: Как создать свой сайт
- BB-код ссылки для форумов (например, можете поставить её в подписи):
[URL=»https://myrusakov.ru»]Как создать свой сайт[/URL]
Источник: https://MyRusakov.ru/mysql-trigger.html
Введение в триггеры MySQL
Цель триггера – дополнительный уровень абстрагирования уровня приложений. Триггер представляет собой именованный объект базы данных, который связан с определенной таблицей, и он будет активизирован, когда для таблицы произойдет одно из событий.
Триггер – это правило, которое помещается в таблицу, и при выполнении DELETE, UPDATE или INSERT совершает дополнительные действия. Например, можно делать запись в журнале об изменении.
Но вместо написания двух отдельных запросов (один – для изменения данных, другой для внесения записи в журнал), можно написать триггер, который будет содержать правило: “Когда бы ни изменялась строка, создать новую строку в другой таблице, чтобы сообщить, что были сделаны изменения”.
Такой подход создает некоторую избыточность в основном запросе, но теперь нет проходов двух разных пакетов до сервера вашей базы данных, чтобы выполнить два разных действия, что в целом способствует улучшению производительности.
Триггеры выполняются как процедуры при событиях UPDATE, DELETE и INSERT. При этом могут быть выполнены либо до, либо после определения события.
Таким образом, Вы можете определить триггер, которые будет выполняться перед DELETE или после DELETE, и так далее.
Это значит, что можно иметь один триггер, который выполнится до INSERT и совершенно другой, который выполнится после INSERT, что является весьма мощным инструментом.
Не может быть двух триггеров для данной таблицы, которые имеют те же самые время действия и событие. Например, Вы не можете иметь два триггера BEFORE UPDATE для таблицы. Но Вы можете иметь BEFORE UPDATE и BEFORE INSERT или BEFORE UPDATE и AFTER UPDATE.
Если несколько команд в триггере, то через точку с запятой.
Триггер контроля целостности данных
Рассмотрим две таблицы 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
Задания для самостоятельной работы
Источник: http://aermolenko.ru/2015/11/vvedenie-v-triggery-mysql/
MySQL. Триггеры
Данная статья посвящена триггерам в MySQL и основана на официальной документации.
Триггер это именованный объект БД, ассоциированный с таблицей и который активируется при insert / update / delete. Причем активация может быть до события и после (before, after). Можно использовать триггеры для проверки значений, вставляемых в таблицу или проводить расчеты.
То есть мы можем создавать триггеры для следующих событий
BEFORE INSERT и AFTER INSERT…
BEFORE UPDATE и AFTER UPDATE
BEFORE DELETE и AFTER DELETE…
|
Пример триггера из документации
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)
|
Этот код выполнен в консоли о чем свидетельствует выражение “mysql>”. Что собственно в этом коде происходит? А все очень просто – создается таблица, а в триггере объявляется аккумулирующее суммирование.
Символ @ перед переменной означает глобальную переменную. Её сервер mysql будет помнить после выхода из триггера и её же можно будет потом вызвать, например так
select @sum;
- NEW означает, что мы берем новое значение, а не старое (для старого используется OLD, но в случае инструкции INSERT его и нет, об этом поговорим ниже)
- FOR EACH ROW это что-то вроде цикла, то есть то, что идет до следующей точки с запятой будет повторяться для каждого ряда таблицы, к которой прикреплен триггер.
- Далее, попробуем вставить данные и посмотреть что произошло.
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 |
+————————+
mysql> SET @sum = 0;mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
|
Сервер произвел расчет примерно таким образом
14.98 + 1937.50 — 100, или 1852.48.
| 14.98 + 1937.50 — 100, или 1852.48. |
Чтобы удалить триггер можно использовать следующую инструкцию
mysql> DROP TRIGGER test.ins_sum;
| mysql> DROP TRIGGER test.ins_sum; |
При удалении таблицы все триггеры, связанные с таблицей удаляются.
OLD and NEW
NEW эти данные только вошли.
OLD данные уже обработаны. Read-only.
Разберемся более внимательно с тем, что такое OLD and NEW. Эти инструкции позволяют получать доступ к колонкам с которыми работает триггер. Эти инструкции относятся исключительно к MySQL. Они не чувствительны к регистру.
В INSERT триггере, только NEW.col_name может быть использовано. Это и понятно, там OLD.col_name ещё и нет.
В DELETE триггере только OLD.col_name может быть использовано.
В UPDATE триггере можно использовать и то и другое.
Более детально можно посмотреть в документации
Следующий пример
Следующий пример позволяет создавать триггеры из нескольких инструкций. Все что нужно это заключить их в BEGIN… …END, а также объявить другой delimiter на время исполнения триггера.
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, но не возвращающую множество.
- Также внутри триггера нельзя использовать транзакции.
- Триггеры могут иметь прямые ссылки на таблицы, например таким образом
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);
|
|
Теперь, допустим, мы произвели вставку данных таким образом
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 |
mysql> SELECT * FROM test1;
+——+
| a1 |
+——+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
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)
|
|
Таким образом, мы рассмотрели основные возможности триггеров.
Источник: http://digital-flame.ru/2016/02/01/mysql-triggeryi/
Триггеры в MySQL
Триггер — это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных.
События к которым можно привязывать триггеры в MySQL: INSERT, UPDATE, DELETE.
Время, в которое будет исполнен триггер может быть: BEFORE (до наступления заданного события) или AFTER (после него).
Во-первых, триггеры появились в MySQL 5.0.2.
Во-вторых, триггеры в MySQL могут создаваться только пользователем с привилегией SUPER, т.е. если Ваше приложение использует триггеры, то ни на одном, даже платном, хостинге оно работать не будет.
Во-третьих, нельзя редактировать таблицу уже открытую для чтения или записи условием инициализации триггера. В этом случае просто используйте оператор SET для нужных полей.
CREATE TRIGGER `artists_litera_autoupdate` AFTER UPDATE ON `cms_artists`
FOR EACH ROW BEGIN
UPDATE `cms_artists` SET `litera` = UPPER(SUBSTRING(NEW.`name`, 1, 1)) WHERE `id` = OLD.`id`;
END; В противном случае Вы получите такую ошибку: #1442 — Can't update table 'tablename' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Кроме того в показанном выше запросе есть ещё одна ошибка, которая после исправления предидущей обязательно появится: ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger В-четвёртых, значения надо менять до обновления. В конечном счёте правильный вариант: CREATE TRIGGER `artists_litera_autoupdate` BEFORE UPDATE ON `cms_artists`
FOR EACH ROW BEGIN
SET NEW.`litera`= UPPER(SUBSTRING(NEW.`name`, 1, 1));
END; В-пятых, при очищении таблицы (TRUNCATE) триггер, повешенный на событие DELETE, исполняться не будет. CREATE TRIGGER — даём понять MySQL'ю что мы от него хотим.
`artists_litera_autoupdate` — имя создаваемого триггера.
BEFORE — время его исполнения.
UPDATE — событие, по наступлению которого, в указанное время, исполняется триггер.
ON `cms_artists` — таблица, к которой привязывается триггер.
FOR EACH ROW BEGIN — определяет выражения (до END;), которые будут применены к каждому ряду затронутому событием, к которому привязан триггер. Т.е. при запросе: UPDATE `news` SET `views` = `views` + 1 WHERE `id` IN (1,2,3) Триггер, повешенный на обновление этой таблицы будет выполнен три раза, если количество записей, удовлетворяющих WHERE равно трём. Тоже самое верно и для множественных вставок: INSERT INTO `posts` (`date`, `title`) VALUES (NOW(), 'Проверка раз'), (NOW(), 'Проверка два') В этом случае триггер, повешенный на INSERT для таблицы `posts` будет выполнен два раза. Задача:
Источник: https://ru-mysql.livejournal.com/254557.html
Использование триггеров в СУБД MySQL
Триггер (англ. 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;
Триггеры имеют несколько важных особенностей использования:
- триггеры в MySQL 5 могут создаваться только пользователем с привилегией SUPER;
- при использовании запроса, затрагивающего N — записей, триггер будет запускаться N — раз;
- после удаления таблицы, СУБД MySQL автоматически удаляет привязанные к ней триггеры.
Список использованный источников
- Официальная документация MySQL (http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html)
- Habrahabr.ru — Триггеры в MySQL (http://habrahabr.ru/blogs/mysql/37693/)
- Википедия (http://ru.wikipedia.org/wiki/Триггер_(базы_данных))
При полном или частичном использовании любых материалов с сайта вы обязаны явным образом указывать ссылку на handyhost.ru в качестве источника.
Источник: https://HandyHost.ru/manuals/mysql/mysql-trigger.html
Блог веб-разработчика
Поддержка триггеров появилась в MySQL 5.0.2. Триггер — это аналог процедуры, который ассоциируется с конкретной таблицей и запускается при наступлении определенных событий связанных с этой самой таблицей. Событий для таблиц всего несколько: INSERT, DELETE, UPDATE.
Пример триггера INSERT для таблицы News — записываем в поле `created_at` дату и время создания записи. Конечно можно было воспользоваться TIMESTAMP, но в данном случае это сделано в качестве простого примера:
DROP TRIGGER IF EXISTS `database`.`news_insert`;
DELIMITER $$
CREATE
DEFINER = CURRENT_USER
TRIGGER `database`.`news_insert` BEFORE INSERT
ON `database`.`news`
FOR EACH ROW BEGIN
SET NEW.`created_at` = NOW();
END$$
DELIMITER ;
В триггере используем оператор DEFINER = CURRENT_USER, его можно опустить, тогда в этом случае будут права текущего пользователя, но можно и установить другого, например:
CREATE
DEFINER = `user`@'localhost'
TRIGGER `database`.`news_insert` BEFORE INSERT
….
До версии MySQL 5.1.6 для создания триггеров требовались полномочия суперпользователя, что не совсем хорошо, т.к. во-первых на шаровых хостингах не предоставляют полномочия суперпользователя, а во-вторых это потенциальная брешь в системе безопасности. Без прав суперпользователя в MySQL ниже 5.1.6 получим ошибку:
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation
Пример создание пользователя в MySQL и предоставления ему обычных прав:
#DROP USER 'user'@'localhost';
# ''
CREATE USER 'user'@'localhost' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4;
GRANT USAGE ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4;
GRANT ALL PRIVILEGES ON `database`.* TO 'user'@'localhost' WITH GRANT OPTION;
Пример создание пользователя и предоставления ему прав суперпользователя:
CREATE USER 'user'@'localhost' IDENTIFIED BY 'my_password';
GRANT USAGE ON *.* TO 'user'@'localhost';
GRANT ALL PRIVILEGES ON `database`.* TO 'user'@'localhost' WITH GRANT OPTION;
GRANT SUPER ON *.* TO 'user'@'localhost';
Ссылки по теме:
• MySQL 5.1 Reference Manual :: 24.1 CREATE TRIGGER Syntax
• MySQL 5.1 Reference Manual :: 12.5.1.3 GRANT Syntax
• PDF: MySQL 5.0 Triggers
• Habrahabr: Триггеры в MySQL
Источник: http://tigor.com.ua/blog/2008/08/27/mysql-triggers/
Загрузка...
