Обзор триггеров Триггеры DML-триггеры DDL-триггеры DML-события: Insert, Delete, Update Logon-триггеры DDL-события: Create, Drop, Alter Logon Появились в SQL Server 2005
DML - trigger Объект - таблица, VIEW Событие - insert, update, delete для таблицы и для VIEW. Время активации – до (вместо) или после выполнения оператора.
DML-триггеры Триггер – блок, выполняемый автоматически каждый раз, когда происходит определенное событие – в отличие от процедуры, которая должна быть вызвана явно Событие – INSERT, UPDATE и DELETE для таблицы, представления – для запроса нельзя определить триггер
DML-триггеры Триггер создается по одной таблице базы данных Может осуществлять доступ и к другим таблицам и объектам других баз данных. Триггеры нельзя создать по временным таблицам или системным таблицам, а только по определенным пользователем таблицам или представлениям. Таблица, по которой определяется триггер, называется таблицей триггера.
Когда нужны триггеры Чтобы оценить состояние таблицы до и после изменения данных и предпринять действия на основе этого различия. Несколько DML-триггеров одинакового типа (INSERT, UPDATE или DELETE) для таблицы позволяют предпринять несколько различных действий в ответ на одну инструкцию изменения данных.
Когда нужны триггеры Для каскадных изменений в связанных таблицах БД (если их нельзя выполнить при помощи каскадных ограничений ссылочной целостности). Для предотвращения случайных или неправильных операций INSERT, UPDATE и DELETE Для реализации ограничений целостности, которые нельзя определить при помощи ограничения CHECK. DML-триггеры могут ссылаться на столбцы других таблиц.
Еще… Журнализация и аудит. С помощью триггеров можно отслеживать изменения таблиц, для которых требуется поддержка повышенного уровня безопасности. Данные об изменении таблиц могут сохраняться в других таблицах и включать, например, идентификатор пользователя, время операции обновления; сами обновляемые данные и т. д. Согласование и очистка данных. С любым простым оператором SQL, обновляющим некоторую таблицу, можно связать триггеры, производящие соответствующие обновления других таблиц. Операции, не связанные с изменением базы данных. В триггерах могут выполняться не только операции обновления базы данных. Стандарт SQL позволяет определять хранимые процедуры (которые могут вызываться из триггеров), посылающие электронную почту, печатающие документы и т. д.
Когда не надо использовать триггеры Не нужно реализовывать триггерами возможности, достигаемые использованием декларативных средств СУБД (ограничения целостности или внешние ключи) Избегайте сложных цепочек триггеров
Советы Не используйте триггеры, если можно применить проверочное ограничение CHECK Не используйте ограничение CHECK, если можно обойтись ограничением UNIQUE.
Основные параметры триггера Имя таблицы (или представления) Время срабатывания: AFTER(FOR) или INSTEAD OF Событие: INSERT, UPDATE, DELETE (TRUNCATE TABLE – это не удаление!) Тело триггера! Последовательность срабатывания однотипных триггеров произвольна
Группировка событий Например, вы можете создать триггер, который будет активизироваться, когда происходит выполнение оператора UPDATE или INSERT, и такой триггер мы будем называть триггером UPDATE/INSERT. Вы можете даже создать триггер, который будет активизироваться при возникновении любого из трех событий модификации данных (триггер UPDATE/INSERT/DELETE).
Правила работы триггера Триггеры запускаются после завершения оператора, который вызвал их активизацию. Например, UPDATE-триггер не будет активизироваться, пока не будет выполнен оператор UPDATE. Если какой-либо оператор пытается выполнить операцию, которая нарушает какое-либо ограничение по таблице или является причиной какой-то другой ошибки, то связанный с ним триггер не будет активизирован.
Правила работы триггера Триггер рассматривается как часть одной транзакции вместе с оператором, который вызывает его. Поэтому из триггера можно вызвать оператор отката, и этот оператор выполнит откат как триггера, так и соответствующего события модификации данных. При возникновении ошибки при выполнении триггера автоматически выполняется откат всей транзакции. Триггер активизируется только один раз для одного оператора, даже если этот оператор влияет на несколько строк данных.
Пример CREATE TRIGGER trg ON my_table FOR INSERT, UPDATE, DELETE AS select "this is trigger"
При вызове триггера будут выполнены операторы SQL, указанные после ключевого слова AS. Вы можете поместить сюда несколько операторов, включая программные конструкции, такие как IF и WHILE.
Выбор типа триггера Триггеры INSTEAD OF используются для: – Выборочного запрещения исполнения команды, для которой определен триггер (проверки предусловия); – Подсчета значений столбцов до завершения команды INSERT или UPDATE. Триггеры AFTER используются для: – Учета выполненных операций; – Проверки пост-условий исполнения команды.
Циклы и вложенность SQL Server позволяет использовать вложенные триггеры, до 32 уровней вложенности. Если любой из вложенных триггеров выполняет операцию ROLLBACK, то последующие триггеры не запускаются. Запуск триггеров отменяется, если формируется бесконечный цикл.
Триггер INSTEAD OF Триггер INSTEAD OF выполняется вместо запуска оператора SQL. Тем самым переопределяется действие запускающего оператора. Можно задать по одному триггеру INSTEAD OF на один оператор INSERT, UPDATE или DELETE. Триггер INSTEAD OF можно задать для таблицы и/или представления Можно использовать каскады триггеров INSTEAD OF, определяя представления поверх представлений, где каждое представление имеет отдельный триггер INSTEAD OF. Триггеры INSTEAD OF не разрешается применять для модифицируемых представлений, содержащих опцию WITH CHECK.
Триггер AFTER Триггеры AFTER могут быть определены только в таблицах. Триггер AFTER активизируется после успешного выполнения всех операций, указанных в запускающем операторе или операторах SQL. Сюда включается весь каскад действий по ссылкам и все проверки ограничений.
Триггер AFTER Если у вас имеется несколько триггеров AFTER, определенных по таблице для определенного оператора или набора операторов, то вы можете задать, какой триггер будет активизирован первым и какой триггер – последним. Если у вас определено больше двух триггеров, то вы можете задать порядок активизации только первого и последнего триггера. Все остальные триггеры активизируются случайным образом.
Порядок AFTER-триггеров sp_settriggerorder @triggername = "Another. Trigger", @order = "first" sp_settriggerorder @triggername = "My. Trigger", @order = "last" sp_settriggerorder @triggername = "My. Other. Trigger", @order = "none" sp_settriggerorder @triggername = "Yet. Another. Trigger", @order = "none"
Использование inserted, deleted Специальные таблицы: inserted – вставленные значения (для INSERT, UPDATE) deleted – удаленные значения (для UPDATE, DELETE)
Использование таблиц deleted и inserted При создании триггера вы имеете доступ к двум временным таблицам с именами deleted и inserted. Они хранятся в памяти, а не на диске. Эти две таблицы имеют одинаковую структуру с таблицей (одинаковые колонки и типы данных), по которой определяется данный триггер.
Использование таблиц deleted и inserted Таблица deleted содержит копии строк, на которые повлиял оператор DELETE или UPDATE. Строки, удаляемые из таблицы данного триггера, перемещаются в таблицу deleted. После этого к данным таблицы deleted можно осуществлять доступ из данного триггера. Таблица inserted содержит копии строк, добавленных к таблице данного триггера при выполнении оператора INSERT или UPDATE. Эти строки добавляются одновременно в таблицу триггера и в таблицу inserted.
Использование таблиц deleted и inserted Поскольку оператор UPDATE обрабатывается как DELETE, после которого следует INSERT, то при использовании оператора UPDATE старые значения строк копируются в таблицу deleted, а новые значения строк – в таблицу триггера и в таблицу inserted. Триггер INSERT => deleted пуст Триггер DELETE => inserted пуст но сообщение об ошибке не возникнет!
Создание триггера CREATE TRIGGER [ schema_name. ]trigger_name ON { table | view } { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement}
CREATE TRIGGER plus_1 ON table 1 instead of insert AS insert table 1 (id, col 1) select id+1, col 1 from inserted;
Обработка исключений Команда ROLLBACK указывает серверу остановить обработку модификации и запретить транзакцию. Существует также команда RAISEERROR, с помощью которой вы можете отправить сообщение об ошибке пользователю. TRY…CATCH
Обработка исключений сообщение об ошибке RAISERROR ("Error raised because of wrong data. ", -- Message text. 16, -- Severity. 1 -- State.); Severity – число от 0 до 25 Определенный пользователем уровень серьезности ошибки. 0 до 18 может указать любой пользователь. 19 до 25 могут быть указаны только sysadmin 20 до 25 считаются неустранимыми - соединение с клиентом обрывается и регистрируется сообщение об ошибке в журналах приложений и ошибок. State Целое число от 0 до 255. Отрицательные значения или значения больше 255 приводят к формированию ошибки. Если одна и та же пользовательская ошибка возникает в нескольких местах, то при помощи уникального номера состояния для каждого местоположения можно определить, в каком месте кода появилась ошибка.
Функции об ошибках Функция ERROR_LINE() возвращает номер строки, в которой произошла ошибка. Функция ERROR_MESSAGE() возвращает текст сообщения, которое будет возвращено приложению. Текст содержит значения таких подставляемых параметров, как длина, имена объектов или время. ERROR_NUMBER() возвращает номер ошибки. Функция ERROR_PROCEDURE() возвращает имя хранимой процедуры или триггера, в котором произошла ошибка. Эта функция возвращает значение NULL, если данная ошибка не была совершена внутри хранимой процедуры или триггера. ERROR_SEVERITY() возвращает уровень серьезности ошибки. ERROR_STATE() возвращает состояние.
Пример триггера CREATE TRIGGER Low. Credit ON Purchasing. Purchase. Order. Header AFTER INSERT AS BEGIN DECLARE @creditrating tinyint, @vendorid int ; SELECT @creditrating = v. Credit. Rating, @vendorid = p. Vendor. ID FROM Purchasing. Purchase. Order. Header p JOIN inserted i ON p. Purchase. Order. ID = i. Purchase. Order. ID JOIN Purchasing. Vendor v ON v. Vendor. ID = i. Vendor. ID ; IF @creditrating = 5 RAISERROR ("This vendor""s credit rating is too low to accept new purchase orders. ", 16, 1) ; END
Управление триггерами Отключение/включение триггера: – DISABLE/ENABLE TRIGGER trigger_name ON object_name Отключение/включение всех триггеров таблицы: – DISABLE/ENABLE TRIGGER ALL ON object_name Изменение триггера: – ALTER TRIGGER trigger_name … Удаление триггера: – DROP TRIGGER trigger_name
Активация/деактивация триггера DISABLE TRIGGER {trigger_name [ , . . . n ] | ALL } ON { object_name} ; ENABLE TRIGGER {trigger_name [ , . . . n ] | ALL } ON { object_name}
Применение триггеров Защита – Запрещение доступа в зависимости от значений данных Учет – Ведение журналов изменений Целостность данных – Сложные правила целостности – Сложная ссылочная целостность Производные данные – автоматическое вычисление значений
Типы триггеров Функция Триггер AFTER Триггер INSTEAD OF Сущности Таблицы и представления Количество триггеров на таблицу/представление Несколько на одно событие Один триггер на одно событие Нет ограничений INSTEAD OF UPDATE и DELETE нельзя определять для таблиц, на которые распространяются каскадные ограничения ссылочной целостности. Каскадные ссылки После следующих операций: Обработка ограничений. Выполнение Декларативные ссылочные действия. Создание таблиц inserted и deleted. Действие, запускающее триггер. Перед следующей операцией: Обработка ограничений. Вместо следующей операции: Действие, запускающее триггер. После следующих операций: Создание таблиц inserted и deleted.
DDL - trigger Триггеры DDL могут быть использованы в административных задачах, таких как аудит и регулирование операций базы данных. Действие этих триггеров распространяется на все команды одного типа во всей базе данных или на всем сервере.
DDL - триггеры Триггеры DDL, как и обычные триггеры, вызывают срабатывание хранимых процедур в ответ на событие. Срабатывают в ответ на разнообразные события языка определения данных (DDL). Эти события в основном соответствуют инструкциям языка Transact-SQL, начинающимся ключевыми словами CREATE, ALTER или DROP.
Задачи для DDL - триггеров Предотвратить внесение определенных изменений в схему базы данных. Выполнить в базе данных некоторые действия в ответ на изменения в схеме базы данных. Записывать изменения или события схемы базы данных. Триггеры DDL срабатывают только после выполнения соответствующих инструкций DDL. Триггеры DDL нельзя использовать в качестве триггеров INSTEAD OF.
CREATE TRIGGER trigger_name ON { DATABASE | ALL SERVER } { FOR | AFTER } { event_type | event_group } AS { sql_statement [ ; ] [ , . . . n ] [ ; ] }
Создание/удаление DDL-тр CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT "Database Created. " DROP TRIGGER ddl_trig_database ON ALL SERVER;
DDL - trigger CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT "You must disable Trigger "safety" to drop or alter tables!" ROLLBACK ;
Для одной инструкции Transact-SQL можно создать несколько триггеров DDL. Триггер DDL и инструкция, приводящая к его срабатыванию, выполняются в одной транзакции. Откат событий ALTER DATABASE, возникших внутри триггера DDL, невозможен. Триггеры DDL выполняются только после завершения инструкции Transact-SQL. Триггеры DDL нельзя использовать в качестве триггеров INSTEAD OF. Триггеры DDL не создают таблицы inserted и deleted.
Logon - trigger Триггеры входа выполняют хранимые процедуры в ответ на событие LOGON. Это событие вызывается при установке пользовательского сеанса с экземпляром SQL Server. Триггеры входа срабатывают после завершения этапа проверки подлинности при входе, но перед тем, как пользовательский сеанс реально устанавливается.
Logon - trigger CREATE TRIGGER trigger_name ON ALL SERVER { FOR| AFTER } LOGON AS { sql_statement }
По материалам статьи Robert Marda на sqlservercentral.com: Auditing Through Triggers
В этой статье Роберт приводит примеры кода для нескольких триггеров, устанавливаемых на таблицы в целях аудита действий пользователей с записями MS SQL Server 7.0/2000.
Для пояснения работы триггеров вообще и как они работают в SQL Server 7.0 и SQL Server 2000, Вы можете обратиться к следующим статьям, написанным Брайеном Келлей (Brian Kelley):
Первая статья объясняет назначение специальных таблиц для вставки и удаления (inserted and deleted
Tables).
Предлагаемые ниже примеры будут работать на SQL Server 2000, однако они были проверены только на
SQL Server 7.0.
Сначала мы должны создать необходимые для дальнейшей работы таблицы. Выполните представленный ниже
скрипт в Query Analyzer:
CREATE TABLE (
IDENTITY (1, 1) NOT NULL ,
NULL ,
(35) NULL
) ON
GO
CREATE TABLE (
NOT NULL ,
(25) NULL ,
(25) NULL ,
(75) NULL ,
(50) NULL ,
NULL ,
(35) NULL ,
) ON
GO
Триггер, отслеживающий операции удаления
Если Вы хотите фиксировать удаления из таблицы, можете воспользоваться примером триггера, который будет вставлять строку в ComponentsDeleted всякий раз, когда строка будет удалена из таблицы
Components:
CREATE TRIGGER deletedby ON dbo.Components
FOR DELETE
AS
INSERT INTO ComponentsDeleted (Iden, ComponentName, SerialNumber,
Comments,
UserName, DeletedDate, DeletedBy)
SELECT Iden, ComponentName, SerialNumber, Comments, UserName, getdate(),
SYSTEM_USER
FROM deleted
Удалите одну или две строки из таблицы Components. Теперь посмотрите таблицу ComponentsDeleted, и Вы увидите там удаленные Вами строки с датой и временем того, когда они были удалены.
Установка простой системы аудита, использующей триггеры, примеры которых представлены в этой статье, может быть полезной в тех случаях, когда Вам необходимо знать, кто и когда выполнил отслеживаемые триггерами действия в вашей базе данных.
Триггер – это специальный тип хранимых процедур, который запускается автоматически при выполнении тех или иных действий с данными таблицы. Каждый триггер привязывается к конкретной таблице. Все производимые триггером модификации данных рассматриваются как одна транзакция, которая откатывается при обнаружении ошибки или нарушении целостности данных, тем самым внесение изменений будет запрещено.
Существует три типа триггеров в зависимости от команд, на которые они реагируют:
1) Триггеры на вставку. Запускаются при попытке вставки данных с помощью команды Insert.
2) Триггеры на обновление. Запускаются при попытке изменения данных с помощью команды Update.
3) Триггеры на удаление. Запускаются при попытке удаления данных с помощью команды Delete.
Для одной таблицы допускается создание нескольких однотипных триггеров. Триггеры могут быть использованы, например, для создания сложных значений по умолчанию, для обеспечения нестандартной ссылочной целостности, поддержание которой обычными средствами Transact SQL невозможно.
Триггеры часто используются для выполнения каскадных изменений в нескольких связанных таблицах. Область применения триггеров не ограничивается строго очерченными рамками. При их создании необходимо руководствоваться требованиями производительности и удобства выполняемых действий. Следует избегать использования триггеров, если те же действия могут быть реализованы с помощью хранимой процедуры или обычных команд Transact SQL. Использование триггеров нежелательно еще и по той причине, что они удерживают блокировку до завершения триггера, запрещая обращение к ресурсу других пользователей. Для создания триггера используется следующая команда Transact SQL:
CREATE TRIGGER trigger_name
sql_statement [...n]
Второй вариант данной команды:
CREATE TRIGGER trigger_name
{FOR { [ [,] }
{ IF UPDATE (column)
UPDATE(column) ]
sql_statement [...n]
Рассмотрим работу первого варианта работы команды Create trigger.
Trigger_name – задает имя триггера, с помощью которого он будет распознаваться хранимыми процедурами и командами Transact SQL. Имя триггера должно быть уникальным в пределах БД.
TABLE – имя таблицы БД, к которой будет привязан триггер.
WITH ENCRYPTION – при указании этой опции сервер выполняет шифрование кода триггера.
[,] [,] – эта конструкция определяет, на какие автоматы будет реагировать триггер. При создании триггера должно быть указано хотя бы одно из этих ключевых слов, допускается создание триггера, реагирующего на две или три команды.
WITH APPEND – указание этого ключевого слова требуется для обеспечения совместимости с более ранними версиями SQL-сервер.
NOT FOR REPLICATION – запрещает запуск триггера при модификации таблиц с помощью репликации.
sql_statement – определяет набор команд, которые будут выполняться при запуске триггера.
Рассмотрим использование второго способа:
FOR { [ [,] – эта инструкция определяет, при выполнении какой команды будет запускаться триггер.
IF UPDATE (column) – использование этого параметра позволяет выполнить триггер при модификации конкретной колонки таблицы.
AND/OR UPDATE(column) – применение совпадает с предыдущим параметром, если необходимо выполнить запуск триггера при модификации нескольких колонок. Аргумент column задает имя колонки, при модификации которой будет производиться запуск триггера. Ключевое слово AND предписывает запускать триггер только в том случае, если были модифицированы обе колонки, указанные в этой и в предыдущей конструкции. При использовании ключевого слова OR триггер будет выполнен при выполнении изменений в любой из колонок. Допускается использование нескольких конструкций AND/OR UPDATE(column).
Для изменения триггера используется команда ALTER TRIGGER:
ALTER TRIGGER trigger_name
{FOR { [,] [,] }
sql_statement [...n]
Перед тем как выполнить команду ALTER TRIGGER следует убедиться в существовании в таблице table триггера.
Для удаления триггера используется команда DROP TRIGGER:
DROP TRIGGER {trigger} [..n]
Единственный аргумент trigger обозначает имя триггера. С помощью одной команды DROP TRIGGER можно удалить несколько триггеров.
Очень может быть, что вы знаете, что такое триггер базы данных, хотя бы в общих терминах. Есть даже шанс, что вы знаете, что 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 ROW
BEGIN
DELETE 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 ROW
BEGIN
INSERT 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 ROW
BEGIN
UPDATE trigger_items
SET 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 ROW
BEGIN
IF NEW.cost < 50 THEN
SET NEW.cost = 50;
ELSEIF NEW.cost > 50 AND NEW.cost < 100 THEN
SET NEW.cost = 100;
END IF;
END
Это не запрос, а перекрытие значений. Если цена меньше $50, то устанавливаем ее $50. Если цена лежит между $50 и $100, то устанавливаем ее $100. Если она выше, то просто оставляем ее такой, какая она есть. Синтаксис не отличается от других серверных языков. Нужно закрыть выражение IF с помощью END IF.
Проверим работу нашего триггера. Если ввести значение стоимости $30, то цена должна быть $50:
Для значения стоимости $85:
Для проверки того, что триггер AFTER UPDATE все еще работает, цена должна быть $130:
Заключение
В данной статье мы только слегка задели айсберг триггеров в MySQL. Они позволяют переносить правила работы с данными с уровня логики приложения на уровень данных.
Может быть, использование триггеров на одностраничном сайте и является суетой, которая отнимает время и силы. Но сложные вэб приложения могут совершенно преобразиться при использовании триггеров.
<Определение_триггера>::= {CREATE | ALTER} TRIGGER имя_триггера ON {имя_таблицы | имя_представления } { { { FOR | AFTER | INSTEAD OF } { [ DELETE] [,] [ INSERT] [,] [ UPDATE] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS sql_оператор[...n] } | { {FOR | AFTER | INSTEAD OF } { [,] } [ WITH APPEND] [ NOT FOR REPLICATION] AS { IF UPDATE(имя_столбца) [ {AND | OR} 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 находятся старые значения строк, которые будут удалены при успешном завершении
