Что такое триггеры sql

Использование триггеров базы данных MySQL в Ubuntu 18.04

Триггер в MySQL — это определяемая пользователем SQL-команда, которая автоматически вызывается во время операций INSERT , DELETE или UPDATE . Код триггера связан с таблицей и уничтожается после удаления таблицы. Вы можете определить время действия триггера и указать, когда его нужно активировать – до или после определенного события базы данных.

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

Вы также можете использовать триггеры, чтобы сохранить правила проверки на уровне базы данных. Это помогает избежать нарушения бизнес-логики при совместном использовании одного источника данных несколькими приложениями. Это значительно уменьшает количество обращений к серверу базы данных, что, в свою очередь, улучшает время отклика приложений. Поскольку сервер базы данных выполняет триггеры, они могут воспользоваться улучшенными ресурсами сервера, такими как RAM и CPU.

В этом обучающем модуле вы научитесь создавать, использовать и удалять различные типы триггеров в вашей базе данных MySQL.

Предварительные требования

Прежде чем начать, убедитесь в наличии следующего:

• Один сервер Ubuntu 18.04, настроенный в соответствии с инструкциями по начальной настройке сервера с Ubuntu 18.04, а также пользователь sudo без прав root.
• База данных MySQL, работающая на вашем сервере в соответствии с инструкцией по установке MySQL на Ubuntu 18.04
• Учетные данные root пользователя для вашей базы данных MySQL.

Шаг 1 — Создание тестовой базы данных

На этом этапе вы создадите тестовую клиентскую базу данных пользователя с несколькими таблицами для демонстрации работы триггеров MySQL.

Более подробно о работе MySQL можно прочитать в инструкции Запросы в MySQL​​​.

Вначале войдите на сервер MySQL как root:

По запросу введите свой root пароль MySQL и нажмите ENTER для продолжения. Когда вы увидите mysql> , выполните следующую команду, чтобы создать базу данных test_db :

Output
Query OK, 1 row affected (0.00 sec) 

Далее переходите к test_db с помощью:

Output
Database changed 

Начинайте с создания таблицы customers . В этой таблице будут храниться записи клиентов, включая customer_id , customer_name и level . Будет два типа клиентов: BASIC и VIP .

Output
Query OK, 0 rows affected (0.01 sec) 

Теперь, добавьте несколько записей в таблицу customers . Для этого выполните следующие команды одну за другой:

После выполнения каждой команды INSERT вы увидите следующий вывод:

Output
Query OK, 1 row affected (0.01 sec) 

Чтобы убедиться, что тестовые записи были успешно вставлены, выполните команду SELECT :

Output
+-------------+---------------+-------+ | customer_id | customer_name | level | +-------------+---------------+-------+ | 1 | JOHN DOE | BASIC | | 2 | MARY ROE | BASIC | | 3 | JOHN DOE | VIP | +-------------+---------------+-------+ 3 rows in set (0.00 sec) 

Затем создайте другую таблицу customers для хранения соответствующей информации об учетной записи клиентов. Таблица будет содержать поля customer_id и status_notes .

Запустите следующую команду:

Далее создайте таблицу sales . В этой таблице будут храниться данные о продажах, имеющих отношение к разным клиентам в столбце customer_id :

Output
Query OK, 0 rows affected (0.01 sec) 

Вы сможете добавить тестовые данные в колонку sales на следующих этапах во время тестирования триггеров. Далее создайте таблицу audit_log для регистрации обновлений, внесенных в таблицу sales при имплементации триггера AFTER UPDATE в шаге 5:

«>Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME )ENGINE=INNODB;

Output
Query OK, 0 rows affected (0.02 sec) 

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

Шаг 2 — Создание триггера Before Insert

На этом этапе вы изучите синтаксис триггера MySQL перед тем, как применить эту логику для создания триггера BEFORE INSERT , который проверяет поле sales_amount перед вставкой данных в таблицу sales .

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

DELIMITER // CREATE TRIGGER [TRIGGER_NAME] [TRIGGER TIME] [TRIGGER EVENT] ON [TABLE] FOR EACH ROW [TRIGGER BODY]// DELIMITER ; 

Структура триггера включает:

DELIMITER // : разделитель MySQL по умолчанию — это ; . Его нужно заменить на что-то другое, для того, чтобы MySQL рассматривал следующие строки, как одну команду, пока не достигнет пользовательского разделителя. В данном примере в качестве разделителя используется // , а стандартный разделитель ; стоит в конце.

[TRIGGER_NAME] : триггер должен иметь имя, и вы можете указать его именно здесь.

[TRIGGER TIME]​​​ : триггер может быть вызван в разные моменты времени. MySQL позволяет определить, когда запускать триггер — до или после операции с базой данных.

[TRIGGER EVENT] : триггеры могут быть вызваны только операциями INSERT , UPDATE и DELETE . Вы можете использовать любое из значений в зависимости от того, чего вы хотите достичь.

[TABLE] : любой триггер, который вы создаете в своей базе данных MySQL, должен быть связан с таблицей.

FOR EACH ROW : этот оператор позволяет MySQL выполнять код триггера для каждой строки, на которую влияет триггер.

[TRIGGER BODY] : код, который выполняется при вызове триггера, называется trigger body​​. Это может быть один SQL-оператор или несколько команд. Обратите внимание, если вы выполняете несколько SQL-операторов в теле триггера, вы должны заключить их в блок BEGIN. END .

Примечание: при создании тела триггера вы можете использовать ключевые слова OLD и NEW для доступа к старым и новым значениям колонки, введенным во время операции INSERT , UPDATE и DELETE . В триггере DELETE может быть использовано только ключевое слово OLD (подробнее об этом в шаге 4).

Теперь вы можете создать свой первый триггер BEFORE INSERT . Триггер будет связан с таблицей sales и будет вызываться перед вставкой записи для проверки sales_amount . Функция триггера состоит в том, чтобы проверить, превышает ли значение sales_amount , вставляемое в таблицу продаж, величину 10000 , и выдать ошибку, если это так.

Убедитесь, что вы вошли на сервер MySQL. Затем введите следующие команды MySQL одну за другой:

Используйте IF. THEN. END IF​​​ для оценки того, находится ли сумма, указанная в операторе INSERT , в пределах вашего диапазона. Триггер может извлечь новое значение sales_amount , используя ключевое слово NEW .

Чтобы вызвать общее сообщение об ошибке, используются следующие строки для информирования пользователя:

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.'; 

Далее вставьте запись sales_amount со значением 11000 в таблицу sales , чтобы проверить, остановит ли триггер операцию:

Output
ERROR 1644 (45000): Sale has exceeded the allowed amount of 10000. 

Эта ошибка показывает, что код триггера работает должным образом.

Теперь попробуйте новую запись со значением 7500 , чтобы проверить правильность действия команды:

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

Output
Query OK, 1 row affected (0.01 sec) 

Для подтверждения вставки данных запустите следующую команду:

Вывод подтверждает вставку данных в таблицу:

Output
+----------+-------------+--------------+ | sales_id | customer_id | sales_amount | +----------+-------------+--------------+ | 1 | 1 | 7500 | +----------+-------------+--------------+ 1 row in set (0.00 sec) 

На этом этапе вы протестировали способность триггеров проверять данные перед вставкой в базу данных.

Теперь поработайте с триггером AFTER INSERT для сохранения связанной информации в разных таблицах.

Шаг 3 — Создание триггера After Insert

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

На этом этапе вы поработаете с таблицей customer_status​​​ , используя триггер AFTER INSERT для ввода связанных клиентских записей.

Для создания триггера AFTER INSERT введите следующие команды:

Output
Query OK, 0 rows affected (0.00 sec) 

Таким образом вы инструктируете MySQL сохранить еще одну запись в таблицу customer_status , как только происходит вставка новой клиентской записи в таблицу customers .

Теперь вставьте новую запись в таблицу customers , чтобы убедиться, что код триггера вызывается:

Output
Query OK, 1 row affected (0.01 sec) 

После успешной вставки записи убедитесь, что запись нового статуса была добавлена в таблицу customer_status :

Output
+-------------+-----------------------------+ | customer_id | status_notes | +-------------+-----------------------------+ | 4 | ACCOUNT OPENED SUCCESSFULLY | +-------------+-----------------------------+ 1 row in set (0.00 sec) 

Вывод подтверждает успешную работу триггера.

Триггер AFTER INSERT​​​ полезен для мониторинга жизненного цикла клиента. В производственной среде учетные записи клиентов могут проходить различные этапы, например открытие, приостановка и закрытие счета.

На следующем этапе вы будете работать с триггерами UPDATE .

Шаг 4 — Создание триггера Before Update

Триггер BEFORE UPDATE схож с триггером BEFORE INSERT , разница заключается в том, когда они вызываются. Вы можете использовать триггер BEFORE UPDATE для проверки бизнес-логики перед обновлением записи. Для проверки используйте таблицу customers , в которую вы уже вставили некоторые данные.

В базе данных есть два типа клиентов. В этом примере после того, как учетная запись клиента будет обновлена до уровня VIP , она не сможет быть понижена до уровня BASIC . Чтобы применить такое правило, создайте триггер BEFORE UPDATE , который будет выполняться перед оператором UPDATE , как показано ниже. Если пользователь базы данных попытается понизить клиента до уровня BASIC с уровня VIP , будет активировано определяемое пользователем исключение.

Введите следующие команды SQL одну за другой, чтобы создать триггер BEFORE UPDATE :

Используйте ключевое слово OLD для фиксации уровня, предоставленного пользователем при выполнении команды UPDATE . Опять же, вы используете IF. THEN. END IF , чтобы сообщить пользователю об общей ошибке.

Далее выполните следующую SQL команду, которая попытается понизить учетную запись клиента, имеющую идентификатор customer_id , равный 3 :

Вы увидите следующий вывод, предоставляющий SET MESSAGE_TEXT :

Output
ERROR 1644 (45000): A VIP customer can not be downgraded. 

Если вы выполните ту же команду для клиента уровня BASIC и попытаетесь повысить учетную запись до уровня VIP , команда выполнится успешно:

Output
Rows matched: 1 Changed: 1 Warnings: 0 

Вы использовали триггер BEFORE UPDATE для применения бизнес-правила. Теперь перейдем к использованию триггера AFTER UPDATE для ведения журнала аудита.

Шаг 5 — Создание триггера After Update

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

Вы создаете триггер, который регистрирует активность обновления таблицы sales . Наша таблица audit_log будет содержать информацию о пользователях MySQL, обновляющих таблицу sales , дату обновления date , а также новые new и старые old значения sales_amount .

Для создания триггера, выполните следующие команды SQL:

Вы вставляете новую запись в таблицу audit_log . Вы используете ключевое слово NEW для получения значения sales_id и нового значения sales_amount . Также вы используете ключевое слово OLD для получения предыдущего значения sales_amount , если вы хотите зарегистрировать обе суммы для аудита.

Команда SELECT USER() извлекает текущего пользователя, выполняющего операцию, а оператор NOW() извлекает значение текущей даты и времени с сервера MySQL.

Теперь, если пользователь попытается обновить значение какой-либо записи в таблице sales , триггер log_sales_updates вставит новую запись в таблицу audit_log .

Давайте создадим новую запись о продажах со случайным значением sales_id , равным 5 , и попробуем обновить ее. Сначала вставьте запись о продажах:

Output
Query OK, 1 row affected (0.00 sec) 

Затем обновите запись:

Вывод должен выглядеть так:

Output
Rows matched: 1 Changed: 1 Warnings: 0 

Теперь выполните следующую команду, чтобы проверить, смог ли триггер AFTER UPDATE зарегистрировать новую запись в таблице audit_log :

Триггер зарегистрировал обновление. Ваш вывод должен показать предыдущую сумму sales_amount и новую сумму new amount​​ , зарегистрированную пользователем, который обновил запись:

Output
+--------+----------+-----------------+------------+----------------+---------------------+ | log_id | sales_id | previous_amount | new_amount | updated_by | updated_on | +--------+----------+-----------------+------------+----------------+---------------------+ | 1 | 5 | 8000 | 9000 | root@localhost | 2019-11-07 09:28:36 | +--------+----------+-----------------+------------+----------------+---------------------+ 1 row in set (0.00 sec) 

Также в таблице вы увидите дату и время, когда было выполнено обновление, что важно для аудита.

Далее вы будете использовать триггер DELETE для обеспечения целостности ссылок на уровне базы данных.

Шаг 2 — Создание триггера Before Delete

Триггеры BEFORE DELETE вызываются до выполнения операции DELETE в таблице. Этот вид триггеров обычно используется для обеспечения целостности ссылок в разных связанных таблицах. Например, каждая запись в таблице sales связана с записью customer_id из таблицы customers . Если пользователь базы данных удалил из таблицы customers запись, у которой есть связанная запись в таблице sales , у вас не будет возможности узнать, какой клиент был связан с этой записью.

Избежать подобных ситуаций и сделать логику более надежной позволит создание триггера BEFORE DELETE . Выполните следующие SQL команды одну за другой:

Теперь попробуйте удалить клиента, у которого есть связанная запись в таблице sales:

В результате вы получите следующий вывод:

Output
ERROR 1644 (45000): The customer has a related sales record. 

Триггер BEFORE DELETE может предотвратить случайное удаление связанной информации в базе данных.

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

Шаг 5 — Создание триггера After Delete

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

Еще один вариант использования триггера AFTER DELETE — удаление связанной информации из других таблиц после удаления записи из базовой таблицы. Например, вы можете установить триггер, который удаляет запись о клиенте, если записи о продажах с соответствующим customer_id будут удалены из таблицы sales . Запустите следующую команду для создания триггера:

Далее запустите следующую команду, чтобы удалить все записи о продажах, связанных с customer_id , равному 2 :

Output
Query OK, 1 row affected (0.00 sec) 

Теперь проверьте, удалились ли записи для этого клиента из таблицы sales :

Вы получите вывод Empty Set , поскольку запись клиента, связанная с customer_id 2 , была удалена триггером:

Output
Empty set (0.00 sec) 

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

Шаг 8 — Удаление триггеров

Как и любой другой объект базы данных, вы можете удалить триггеры с помощью команды DROP . Синтакс удаления триггера следующий:

Drop trigger [TRIGGER NAME]; 

Например, чтобы удалить последний созданный триггер AFTER DELETE , выполните следующую команду:

Output
Query OK, 0 rows affected (0.00 sec) 

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

Заключение

В этом обучающем руководстве вы научились создавать, использовать и удалять различные триггеры из базы данных MySQL. На примере клиентской базы данных вы ознакомились с применением триггеров для различных целей, таких как проверка данных, применение бизнес-логики, ведение журнала аудита и обеспечение целостности ссылок.

Дополнительную информацию по использованию вашей базы данных MySQL можно найти здесь:

• Оптимизация работы MySQL с помощью кеша запросов в Ubuntu 18.04
• Применение постраничного ввода данных на MySQL с PHP в Ubuntu 18.04​​​
• Устранение ошибок на MySQL

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Триггеры: создание и применение

В реализации СУБД MS SQL Server используется следующий оператор создания или изменения триггера :

::= TRIGGER имя_триггера ON [WITH ENCRYPTION ] < < < FOR | AFTER | INSTEAD OF > < [ DELETE] [,] [ INSERT] [,] [ UPDATE] >[ WITH APPEND ] [ NOT FOR REPLICATION ] AS sql_оператор[. n] > | <  < [INSERT] [,] [UPDATE] >[ WITH APPEND] [ NOT FOR REPLICATION] AS < IF UPDATE(имя_столбца) [ UPDATE(имя_столбца)] [. n] | IF (COLUMNS_UPDATES() бит_маска_изменения) бит_маска [. n]> sql_оператор [. n] > >

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

Рассмотрим назначение аргументов из команды CREATE | ALTER TRIGGER .

Имя триггера должно быть уникальным в пределах базы данных . Дополнительно можно указать имя владельца.

При указании аргумента WITH ENCRYPTION сервер выполняет шифрование кода триггера , чтобы никто, включая администратора, не мог получить к нему доступ и прочитать его. Шифрование часто используется для скрытия авторских алгоритмов обработки данных, являющихся интеллектуальной собственностью программиста или коммерческой тайной.

Типы триггеров

В SQL Server существует два параметра, определяющих поведение триггеров :

• AFTER . Триггер выполняется после успешного выполнения вызвавших его команд. Если же команды по какой-либо причине не могут быть успешно завершены, триггер не выполняется. Следует отметить, что изменения данных в результате выполнения запроса пользователя и выполнение триггера осуществляется в теле одной транзакции: если произойдет откат триггера , то будут отклонены и пользовательские изменения. Можно определить несколько AFTER -триггеров для каждой операции ( INSERT , UPDATE , DELETE ). Если для таблицы предусмотрено выполнение нескольких AFTER -триггеров, то с помощью системной хранимой процедуры sp_settriggerorder можно указать, какой из них будет выполняться первым, а какой последним. По умолчанию в SQL Server все триггеры являются AFTER -триггерами.
• INSTEAD OF . Триггер вызывается вместо выполнения команд. В отличие от AFTER -триггера INSTEAD OF -триггер может быть определен как для таблицы, так и для представления. Для каждой операции INSERT , UPDATE , DELETE можно определить только один INSTEAD OF -триггер.

Триггеры различают по типу команд, на которые они реагируют.

Существует три типа триггеров :

• INSERT TRIGGER – запускаются при попытке вставки данных с помощью команды INSERT .
• UPDATE TRIGGER – запускаются при попытке изменения данных с помощью команды UPDATE .
• DELETE TRIGGER – запускаются при попытке удаления данных с помощью команды DELETE .

Конструкции [ DELETE] [,] [ INSERT] [,] [ UPDATE] и FOR | AFTER | INSTEAD OF > триггер . При его создании должна быть указана хотя бы одна команда. Допускается создание триггера , реагирующего на две или на все три команды.

Аргумент WITH APPEND позволяет создавать несколько триггеров каждого типа.

При создании триггера с аргументом NOT FOR REPLICATION запрещается его запуск во время выполнения модификации таблиц механизмами репликации.

Конструкция AS sql_оператор[. n] определяет набор SQL- операторов и команд, которые будут выполнены при запуске триггера .

Отметим, что внутри триггера не допускается выполнение ряда операций, таких, например, как:

• создание, изменение и удаление базы данных;
• восстановление резервной копии базы данных или журнала транзакций.

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

Программирование триггера

При выполнении команд добавления, изменения и удаления записей сервер создает две специальные таблицы: inserted и deleted . В них содержатся списки строк, которые будут вставлены или удалены по завершении транзакции. Структура таблиц inserted и deleted идентична структуре таблиц, для которой определяется триггер . Для каждого триггера создается свой комплект таблиц inserted и deleted , поэтому никакой другой триггер не сможет получить к ним доступ. В зависимости от типа операции, вызвавшей выполнение триггера , содержимое таблиц inserted и deleted может быть разным:

• команда INSERT – в таблице inserted содержатся все строки, которые пользователь пытается вставить в таблицу; в таблице deleted не будет ни одной строки; после завершения триггера все строки из таблицы inserted переместятся в исходную таблицу;
• команда DELETE – в таблице deleted будут содержаться все строки, которые пользователь попытается удалить; триггер может проверить каждую строку и определить, разрешено ли ее удаление; в таблице inserted не окажется ни одной строки;
• команда UPDATE – при ее выполнении в таблице deleted находятся старые значения строк, которые будут удалены при успешном завершении триггера . Новые значения строк содержатся в таблице inserted . Эти строки добавятся в исходную таблицу после успешного выполнения триггера .

Для получения информации о количестве строк, которое будет изменено при успешном завершении триггера , можно использовать функцию @@ ROWCOUNT ; она возвращает количество строк, обработанных последней командой. Следует подчеркнуть, что триггер запускается не при попытке изменить конкретную строку, а в момент выполнения команды изменения. Одна такая команда воздействует на множество строк, поэтому триггер должен обрабатывать все эти строки.

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

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

Для получения списка столбцов, измененных при выполнении команд INSERT или UPDATE , вызвавших выполнение триггера , можно использовать функцию COLUMNS_UPDATED() . Она возвращает двоичное число, каждый бит которого, начиная с младшего, соответствует одному столбцу таблицы (в порядке следования столбцов при создании таблицы). Если бит установлен в значение «1», то соответствующий столбец был изменен. Кроме того, факт изменения столбца определяет и функция UPDATE (имя_столбца).

Для удаления триггера используется команда

Использование триггеров в СУБД 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;

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

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 в качестве источника.

Все способы

© 2009–2023 «HANDYHOST.RU» 8-800-505-68-01

• Услуги
• Хостинг сайтов
• Домены
• Конструктор сайтов
• Linux VPS / Windows VPS
• Выделенные серверы
• SSL сертификаты

• Клиентам
• Контакты
• О компании
• Акции
• Оборудование
• Партнерская программа

• Поддержка
• Способы оплаты
• Регламент
• Документы
• Справка

Общие сведения о триггерах

где plsql_trigger_source, это такая конструкция:

Конструкции simple_dml_trigger, instead_of_dml_trigger, compound_dml_trigger и system_trigger будут приведены в соответствующих разделах статьи.

DML triggers

• DML триггеры создаются для таблиц или представлений, срабатывают при вставке, обновлении или удалении записей.
• Триггер может быть создан в другой схеме, отличной от той, где определена таблицы. В таком случае текущей схемой при выполнении триггера считается схема самого триггера.
• При операции MERGE срабатывают триггеры на изменение, вставку или удаление записей в зависимости от операции со строкой.
• Триггер – часть транзакции, ошибка в триггере откатывает операцию, изменения таблиц в триггере становятся частью транзакции.
• Если откатывается транзакция, изменения триггера тоже откатываются.
• В триггерах запрещены операторы DDL и управления транзакциями (исключения – автономные транзакции).

Конструкция simple_dml_trigger:

Где, dml_event_clause:

referencing_clause:

trigger_edition_clause:

trigger_body:

• На таблице
• На представлении (instead of trigger)

• Вставка записей (insert)
• Обновление записей (update)
• Удаление записей (delete)

• Уровень всей команды (statement level triggers)
• Уровень записи (row level triggers)
• Составные триггеры (compound triggers)

• Перед выполнением операции (before)
• После выполнения операции (after)

Crossedition triggers — служат для межредакционного взаимодействия, например для переноса и трансформации данных из полей, отсутствующих в новой редакции, в другие поля.

Условные предикаты для определения операции, на которую сработал триггер:

Предикат	Описание
Inserting	True, если триггер сработал на операцию Insert
Updating	True, если триггер сработал на операцию Update
Updating(‘colum’)	True, если триггер сработал на операцию Update, которая затрагивает определенное поле
Deleting	True, если триггер сработал на операцию Delete

Эти предикаты могут использоваться везде, где можно использовать Boolean выражения.

Пример

CREATE OR REPLACE TRIGGER t BEFORE INSERT OR UPDATE OF salary, department_id OR DELETE ON employees BEGIN CASE WHEN INSERTING THEN DBMS_OUTPUT.PUT_LINE('Inserting'); WHEN UPDATING('salary') THEN DBMS_OUTPUT.PUT_LINE('Updating salary'); WHEN UPDATING('department_id') THEN DBMS_OUTPUT.PUT_LINE('Updating department ID'); WHEN DELETING THEN DBMS_OUTPUT.PUT_LINE('Deleting'); END CASE; END; 

Псевдозаписи

Существуют псевдозаписи, позволяющие обратиться к полям изменяемой записи и получить значения полей до изменения и значения полей после изменения. Это записи old и new. С помощью конструкции Referencing можно изменить их имена. Структура этих записей tablename%rowtype. Эти записи есть только у триггеров row level или у compound триггеров (с секциями уровня записи).

Операция срабатывания триггера	OLD.column	NEW.column
Insert	Null	Новое значение
Update	Старое значение	Новое значение
Delete	Старое значение	Null

• С псевдозаписями запрещены операции уровня всей записи ( :new = null;)
• Нельзя изменять значения полей записи old
• Если триггер срабатывает на delete, нельзя изменить значения полей записи new
• В триггере after нельзя изменить значения полей записи new

Instead of dml triggers

• Создаются для представлений (view) и служат для замещения DML операций своим функционалом.
• Позволяют производить операции вставки/обновления или удаления для необновляемых представлений.

Конструкция instead_of_dml_trigger:

• Это всегда триггер уровня записи (row level)
• Имеет доступ к псевдозаписям old и new, но не может изменять их
• Заменяет собой dml операцию с представлением (view)

Пример

CREATE OR REPLACE VIEW order_info AS SELECT c.customer_id, c.cust_last_name, c.cust_first_name, o.order_id, o.order_date, o.order_status FROM customers c, orders o WHERE c.customer_id = o.customer_id; CREATE OR REPLACE TRIGGER order_info_insert INSTEAD OF INSERT ON order_info DECLARE duplicate_info EXCEPTION; PRAGMA EXCEPTION_INIT (duplicate_info, -00001); BEGIN INSERT INTO customers (customer_id, cust_last_name, cust_first_name) VALUES ( :new.customer_id, :new.cust_last_name, :new.cust_first_name); INSERT INTO orders (order_id, order_date, customer_id) VALUES ( :new.order_id, :new.order_date, :new.customer_id); EXCEPTION WHEN duplicate_info THEN RAISE_APPLICATION_ERROR ( num=> -20107, msg=> 'Duplicate customer or order ID'); END order_info_insert; 

Instead of triggers on Nested Table Columns of Views

Можно создать триггер для вложенной в представлении таблицы. В таком триггере также присутствует дополнительная псевдозапись – parent, которая ссылается на всю запись представления (стандартные псевдозаписи old и new ссылаются только на записи вложенной таблицы)

Пример такого триггера

-- Create type of nested table element: CREATE OR REPLACE TYPE nte AUTHID DEFINER IS OBJECT ( emp_id NUMBER(6), lastname VARCHAR2(25), job VARCHAR2(10), sal NUMBER(8,2) ); / -- Created type of nested table: CREATE OR REPLACE TYPE emp_list_ IS TABLE OF nte; / -- Create view: CREATE OR REPLACE VIEW dept_view AS SELECT d.department_id, d.department_name, CAST (MULTISET (SELECT e.employee_id, e.last_name, e.job_id, e.salary FROM employees e WHERE e.department_id = d.department_id ) AS emp_list_ ) emplist FROM departments d; -- Create trigger: CREATE OR REPLACE TRIGGER dept_emplist_tr INSTEAD OF INSERT ON NESTED TABLE emplist OF dept_view REFERENCING NEW AS Employee PARENT AS Department FOR EACH ROW BEGIN -- Insert on nested table translates to insert on base table: INSERT INTO employees ( employee_id, last_name, email, hire_date, job_id, salary, department_id ) VALUES ( :Employee.emp_id, -- employee_id :Employee.lastname, -- last_name :Employee.lastname || '@company.com', -- email SYSDATE, -- hire_date :Employee.job, -- job_id :Employee.sal, -- salary :Department.department_id -- department_id ); END; 

Запускает триггер оператор insert

INSERT INTO TABLE ( SELECT d.emplist FROM dept_view d WHERE department_id = 10 ) VALUES (1001, 'Glenn', 'AC_MGR', 10000); 

Составные DML триггера (compound DML triggers)

Появившиеся в версии 11G эти триггера включают в одном блоке обработку всех видов DML триггеров.
Конструкция compound_dml_trigger:

Где, compound_trigger_block:

timing_point_section:

timing_point:

tps_body:

• Срабатывают такие триггера при разных событиях и в разные моменты времени (на уровне оператора или строки, при вставке/обновлении/удалении, до или после события).
• Не могут быть автономными транзакциями.

• Собирать в коллекцию строки для вставки в другую таблицу, чтобы периодически вставлять их пачкой
• Избежать ошибки мутирующей таблицы (mutating-table error)

Структура составного триггера

• Before statement
• After statement
• Before each row
• After each row

• Нельзя обращаться к псевдозаписям old, new или parent в секциях уровня выражения (before statement и after statement)
• Изменять значения полей псевдозаписи new можно только в секции before each row
• Исключения, сгенерированные в одной секции, нельзя обрабатывать в другой секции
• Если используется оператор goto, он должен указывать на код в той же секции

Пример

create or replace trigger tr_table_test_compound for update or delete or insert on table_test compound trigger v_count pls_integer := 0; before statement is begin dbms_output.put_line ( 'before statement' ); end before statement; before each row is begin dbms_output.put_line ( 'before insert' ); end before each row; after each row is begin dbms_output.put_line ( 'after insert' ); v_count := v_count + 1; end after each row; after statement is begin dbms_output.put_line ( 'after statement' ); end after statement; end tr_table_test_compound; 

Основные правила определения DML триггеров

• Update of – позволяет указать список изменяемых полей для запуска триггера
• Все условия в заголовке и When … проверяются без запуска триггера на стадии выполнения SQL
• В операторе When можно использовать только встроенные функции
• Можно делать несколько триггеров одного вида, порядок выполнения не определен по умолчанию, но его можно задать с помощью конструкции FOLLOWS TRIGGER_FIRST
• Ограничения уникальности проверяются при изменении записи, то есть после выполнения триггеров before
• Секция объявления переменных определяется словом DECLARE
• Основной блок триггера подчиняется тем же правилам, что и обычные PL/SQL блоки

Ограничения DML триггеров

• нельзя выполнять DDL statements (только в автономной транзакции)
• нельзя запускать подпрограммы с операторами контроля транзакций
• не имеет доступа к SERIALLY_REUSABLE пакетов
• размер не может превышать 32К
• нельзя декларировать переменные типа LONG и LONG RAW

Ошибка мутирования таблицы ORA-04091

• использовать триггеры уровня операции
• автономная транзакция в триггере
• использовать сторонние структуры (коллекции уровня пакета)
• использовать COMPOUND TRIGGER
• изменение самого алгоритма с выносом функционала из триггера

Системные триггеры (System triggers)

Конструкция system_trigger:

Такие триггеры относятся или к схеме, или ко всей базе данных.

• До того, как будет выполнена операция (на которую срабатывает триггер)
• После того, как будет выполнена операция (на которую срабатывает триггер)
• Вместо выполнения оператора Create

Триггеры уровня схемы (schema triggers)

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

Пример триггера

CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA BEGIN RAISE_APPLICATION_ERROR ( num => -20000, msg => 'Cannot drop object'); END; 

Триггеры уровня базы данных (database triggers)

• Такой триггер срабатывает когда любой пользователь БД выполняет команду, на которую создан триггер.

Пример триггера

CREATE OR REPLACE TRIGGER check_user AFTER LOGON ON DATABASE BEGIN check_user; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack); END; 

Instead of create triggers

• Это триггер уровня схемы, который срабатывает на команду create и заменяет собой эту команду (т.е. вместо выполнения команды create выполняется тело триггера).

Пример триггера

CREATE OR REPLACE TRIGGER t INSTEAD OF CREATE ON SCHEMA BEGIN EXECUTE IMMEDIATE 'CREATE TABLE T (n NUMBER, m NUMBER)'; END; 

Атрибуты системных триггеров

IF (ora_sysevent = 'LOGON') THEN v_addr := ora_client_ip_address; END IF; 

v_db_name := ora_database_name;

IF (ora_dict_obj_type = 'USER') THEN INSERT INTO event_table VALUES (ora_des_encrypted_password); END IF; 

INSERT INTO event_table VALUES ('Changed object is ' || ora_dict_obj_name); 

IF (ora_sysevent='ASSOCIATE STATISTICS') THEN number_modified := ora_dict_obj_name_list(name_list); END IF; 

INSERT INTO event_table VALUES ('object owner is' || ora_dict_obj_owner); 

IF (ora_sysevent='ASSOCIATE STATISTICS') THEN number_modified := ora_dict_obj_name_list(owner_list); END IF; 

INSERT INTO event_table VALUES ('This object is a ' || ora_dict_obj_type); 

IF (ora_sysevent = 'GRANT') THEN number_of_grantees := ora_grantee(user_list); END IF; 

IF (ora_instance_num = 1) THEN INSERT INTO event_table VALUES ('1'); END IF; 

IF (ora_sysevent = 'ALTER' AND ora_dict_obj_type = 'TABLE') THEN alter_column := ora_is_alter_column('C'); END IF; 

IF (ora_sysevent = 'CREATE' AND ora_dict_obj_type = 'TABLE' AND ora_is_creating_nested_table) THEN INSERT INTO event_table VALUES ('A nested table is created'); END IF; 

IF (ora_sysevent = 'ALTER' AND ora_dict_obj_type = 'TABLE') THEN drop_column := ora_is_drop_column('C'); END IF; 

IF ora_is_servererror(error_number) THEN INSERT INTO event_table VALUES ('Server error!!'); END IF; 

SELECT ora_login_user FROM DUAL;

-- Retrieve ora_sql_txt into sql_text variable v_n := ora_partition_pos; v_new_stmt := SUBSTR(sql_text,1,v_n - 1) || ' ' || my_partition_clause || ' ' || SUBSTR(sql_text, v_n)); 

IF (ora_sysevent = 'GRANT' OR ora_sysevent = 'REVOKE') THEN number_of_privileges := ora_privilege_list(privilege_list); END IF; 

IF (ora_sysevent = 'REVOKE') THEN number_of_users := ora_revokee(user_list); END IF; 

INSERT INTO event_table VALUES ('top stack error ' || ora_server_error(1)); 

n := ora_server_error_depth; -- Use n with functions such as ora_server_error 

INSERT INTO event_table VALUES ('top stack error message' || ora_server_error_msg(1)); 

n := ora_server_error_num_params(1);

-- Second %s in "Expected %s, found %s": param := ora_server_error_param(1,2); 

CREATE TABLE event_table (col VARCHAR2(2030)); DECLARE sql_text ora_name_list_t; n PLS_INTEGER; v_stmt VARCHAR2(2000); BEGIN n := ora_sql_txt(sql_text); FOR i IN 1..n LOOP v_stmt := v_stmt || sql_text(i); END LOOP; INSERT INTO event_table VALUES ('text of triggering statement: ' || v_stmt); END; 

INSERT INTO event_table VALUES (ora_sysevent); 

IF (ora_sysevent = 'GRANT' AND ora_with_grant_option = TRUE) THEN INSERT INTO event_table VALUES ('with grant option'); END IF; 

IF (ora_space_error_info ( eno,typ,owner,ts,obj,subobj) = TRUE) THEN DBMS_OUTPUT.PUT_LINE('The object '|| obj || ' owned by ' || owner || ' has run out of space.'); END IF; 

События срабатывания системных триггеров

Компиляция триггеров

• Триггер создан в состоянии disabled (или переведен в такое состояние)
• Событие триггера after startup on database
• Событие триггера after logon on database или after logon on schema и происходит попытка залогиниться под пользователем System

Исключения в триггерах

• Если событие триггера after startup on database или before shutdown on database
• Если событие триггера after logon on database и пользователь имеет привилегию administer database trigger
• Если событие триггера after logon on schema и пользователь или является владельцем схемы, или имеет привилегию alter any trigger

Порядок выполнения триггеров

Конструкция trigger_ordering_clause:

1. Сначала выполняются все before statement триггера
2. Потом все before each row триггера
3. После все after each row триггера
4. И в конце все after statement триггера

Включение/отключение триггеров

Это может понадобиться, например, для загрузки большого объема информации в таблицу.
Выполнить включение/отключение триггера можно с помощью команды:

ALTER TRIGGER [schema.]trigger_name < ENABLE | DISABLE >; 

Чтобы включить/отключить сразу все триггеры на таблице:

ALTER TABLE table_name < ENABLE | DISABLE >ALL TRIGGERS; 

Для изменения триггера можно или воспользоваться командой Create or replace trigger, или сначала удалить триггер drop trigger, а потом создать заново create trigger.
Операция alter trigger позволяет только включить/отключить триггер, скомпилировать его или переименовать.
Компиляция триггера:

alter trigger TRIGGER_NAME compile; 

Права для операций с триггерами

Для работы с триггерами даже в своей схеме необходима привилегия create trigger, она дает права на создание, изменение и удаление.

grant create trigger to USER; 

Для работы с триггерами во всех других схемах необходима привилегия * any trigger. Обратите внимание, что права даются отдельно на создание, изменение и удаление.

grant create any trigger to USER; grant alter any trigger to USER; grant drop any trigger to USER; 

Для работы с системными триггерами уровня DATABASE необходима привилегия ADMINISTER DATABASE TRIGGER.

grant ADMINISTER DATABASE TRIGGER to USER; 

Словари данных с информацией о триггерах:

• dba_triggers – информация о триггерах
• dba_source — код тела триггера
• dba_objects – валидность триггера

Видео-запись лекции, по материалам которой и была написана эта статья:

Множество других видео по темам Oracle можно найти на этом канале: www.youtube.com/c/MoscowDevelopmentTeam

Другие статьи по Oracle