Проблема, с которой я столкнулся, довольно известна. Я имею два триггера, которые должны отработать в предопределенном порядке, т.е. триггер a должен выполниться сначала, а после него должен отработать триггер b. Вы можете поинтересоваться, а почему бы не иметь один триггер, который объединит триггеры a и b в один триггер ab? Хороший вопрос. К сожалению, триггер a используется для репликации (for replication), в то время как более поздний триггер — не для репликации, что определяет наличие именно двух триггеров.

Давайте создадим испытательную среду. Для этого нам понадобятся две таблицы. Одна — для написания и тестирования триггеров, а вторая — для журнализации триггера и времени его выполнения.

create table ( identity (1, 1) not null , null , null , null , (1) null) on go

Один триггер будет триггером на вставку, который будет обновлять столбец first некоторым случайным числом. Этот триггер будет называться trg_updatefirst

create trigger trg_updatefirst on dbo. for insert as declare @id int, @val as float select @id = id from inserted select @val = floor(rand() * 10) update set = @val where id = @id insert into triggerlog (triggername) values ("trg_updatefirst")

Последняя строка триггера журнализирует имя триггера и время его срабатывания в таблице triggerlog. Следующий триггер используется для обновления столбца second значением из столбца first.

Последний триггер используется для обновления столбца last суммой значений столбцов first и second.

create trigger trg_updatelast on dbo. for insert as declare @id int select @id = id from inserted update set = + where id = @id insert into triggerlog (triggername) values ("trg_updatelast")

Теперь, чтобы получить ожидаемые результаты, триггеры trg_updatefisrt, trg_updatesecond и trg_updatelast должны выполняться в вышеперечисленном порядке. Что вы об этом думаете? Каков будет порядок? Случайным образом или в некотором особом порядке?

Прежде чем ответить на этот вопрос, давайте посмотрим, что произойдет. После вставки одной записи в таблицу первый столбец содержит пятерку, что нормально, и второй тоже — 5, что также правильно. Однако в последнем столбце находится null! Почему? Разве не должно быть 10?

Теперь давайте проверим таблицу triggerlog. Порядок столбцов — trg_updatelast, trg_updatefirst и trg_updatesecond. После небольшого исследования выясняется, что триггеры выполняются в том порядке, в котором они были созданы. Таким образом, в идеале триггеры следует создавать в таком порядке: trg_updatefirst, trg_updatesecond и trg_updatelast. Это ни в коем случае не является простой задачей в силу динамического характера процесса разработки, который по большей части не контролируется разработчиками.

Другой вопрос. Как на более поздней стадии Вы собираетесь узнать о порядке срабатывания триггеров?

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

Установить порядок

Теперь вопрос о том, как мы можем задать порядок выполнения. Имеется хранимая системная процедура, которая для того и существует, чтобы ответить на подобный вопрос. Эта хранимая процедура — sp_settriggerorder. У этой sp есть три параметра.

sp_settriggerorder [@triggername =] "triggername" , [@order =] "значение" , [@stmttype =] "statement_type"

Первый параметр — это имя триггера, а второй параметр — порядок. Этот порядок может принимать одно из трех значений: " first (первый)", "none (ни первый, ни последний)", и " last (последний)". Последний параметр представляет собой тип триггера, т.е. insert, update или delete. Это означает, что Вы не можете позволить себе иметь четыре или пять триггеров одного и того же типа, которые бы выполнялись в определенном порядке. Однако это вряд ли встречается в практике. По крайней мере, я не встречал еще так много триггеров одного типа на одной таблице.

Этот порядок не может быть установлен опциями alter trigger или create trigger. Если оператор alter trigger изменяет первый или последний триггер, то первоначально установленные на триггере атрибуты first или last удаляются, и значение заменяется на none. Значение порядка должно быть переустановлено с помощью хранимой процедуры sp_settriggerorder.

Разрешения

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

Получение порядка

Следующая проблема заключается в установлении порядка, в котором выполняются триггеры, на более поздней стадии. Если я не ошибаюсь, нет никакого прямого способа получить эту информацию из enterprise manager sql server. Вместо этого Вы пишете простые запросы.

select objectproperty(object_id(" trg_updatefirst "), "execisfirstinserttrigger") execisfirstinserttrigger")

укажет, является ли trg_updatefirst первым (first) триггером на вставку?

Предостережения

Когда Вы генерируете скрипт для триггеров, приоритет их срабатывания не будет заскриптован. Это означает, что Вам придется повторно запустить приоритетные сценарии. Это аргумент в пользу отказа от приоритетных триггеров.

Сервер sql 2005

В sql server 2005 имеется дополнительный параметр в sp_settriggerorder, который должен сообщить, является ли данный триггер триггером базы данных или триггером сервера. Это обусловлено тем, что в sql server 2005 Вы можете написать также ddl триггеры.

Заключение

Установка приоритетного порядка для триггера sql server не составляет труда. Однако Вы должны взять на себя дополнительную заботу, принимая эту возможность в структуре вашей разработки. Будет хорошо, если microsoft может обеспечить решения для рассмотренных выше проблем.

Дается определение триггера, область его использования, место и роль триггера в обеспечении целостности данных. Описываются типы триггеров. Рассматриваются операторы создания, изменения, удаления триггера. Программирование триггера иллюстрируется примерами создания триггеров для реализации ограничений целостности и сбора статистических данных.

Определение триггера в стандарте языка SQL

Триггеры являются одной из разновидностей хранимых процедур. Их исполнение происходит при выполнении для таблицы какого-либо оператора языка манипулирования данными (DML). Триггеры используются для проверки целостности данных, а также для отката транзакций.

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

Триггеры – особый инструмент SQL-сервера, используемый для поддержания целостности данных в базе данных. С помощью ограничений целостности, правил и значений по умолчанию не всегда можно добиться нужного уровня функциональности. Часто требуется реализовать сложные алгоритмы проверки данных, гарантирующие их достоверность и реальность. Кроме того, иногда необходимо отслеживать изменения значений таблицы, чтобы нужным образом изменить связанные данные. Триггеры можно рассматривать как своего рода фильтры, вступающие в действие после выполнения всех операций в соответствии с правилами, стандартными значениями и т.д.

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

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

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

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

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

Основной формат команды CREATE TRIGGER показан ниже:

<Определение_триггера>::= CREATE TRIGGER имя_триггера BEFORE | AFTER <триггерное_событие> ON <имя_таблицы> <тело_триггера>

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

Выполняемые триггером действия задаются для каждой строки (FOR EACH ROW ), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT ).

Обозначение <список_старых_или_новых_псевдонимов> относится к таким компонентам, как старая или новая строка (OLD / NEW ) либо старая или новая таблица (OLD TABLE / NEW TABLE ). Ясно, что старые значения не применимы для событий вставки, а новые – для событий удаления.

При условии правильного использования триггеры могут стать очень мощным механизмом. Основное их преимущество заключается в том, что стандартные функции сохраняются внутри базы данных и согласованно активизируются при каждом ее обновлении. Это может существенно упростить приложения. Тем не менее следует упомянуть и о присущих триггеру недостатках:

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

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

Реализация триггеров в среде MS SQL Server

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

<Определение_триггера>::= {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 находятся старые значения строк, которые будут удалены при успешном завершении триггера . Новые значения строк содержатся в таблице inserted . Эти строки добавятся в исходную таблицу после успешного выполнения триггера .

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

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

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

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

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

DROP TRIGGER {имя_триггера} [,...n]

Приведем примеры использования триггеров .

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

Команда вставки записи в таблицу Сделка может быть, например, такой:

INSERT INTO Сделка VALUES (3,1,-299,"01/08/2002")

Создаваемый триггер должен отреагировать на ее выполнение следующим образом: необходимо отменить команду, если в таблице Склад величина остатка товара оказалась меньше продаваемого количества товара с введенным кодом (в примере код товара=3 ). Во вставляемой записи количество товара указывается со знаком "+", если товар поставляется, и со знаком "-", если он продается. Представленный триггер настроен на обработку только одной добавляемой записи.

CREATE TRIGGER Триггер_ins ON Сделка FOR INSERT AS IF @@ROWCOUNT=1 BEGIN IF NOT EXISTS(SELECT * FROM inserted WHERE -inserted.количество<=ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "Отмена поставки: товара на складе нет" END END Пример 14.1. Использование триггера для реализации ограничений на значение.

Пример 14.2. Использования триггера для сбора статистических данных.

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

INSERT INTO Сделка VALUES (3,1,200,"01/08/2002")

поставляется товар с кодом 3 от клиента с кодом 1 в количестве 200 единиц.

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

ALTER TRIGGER Триггер_ins ON Сделка FOR INSERT AS DECLARE @x INT, @y INT IF @@ROWCOUNT=1 --в таблицу Сделка добавляется запись --о поставке товара BEGIN --количество проданного товара должно быть не --меньше, чем его остаток из таблицы Склад IF NOT EXISTS(SELECT * FROM inserted WHERE -inserted.количество< =ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "откат товара нет " END --если записи о поставленном товаре еще нет, --добавляется соответствующая запись --в таблицу Склад IF NOT EXISTS (SELECT * FROM Склад С, inserted i WHERE С.КодТовара=i.КодТовара) INSERT INTO Склад (КодТовара,Остаток) ELSE --если запись о товаре уже была в таблице --Склад, то определяется код и количество --товара издобавленной в таблицу Сделка записи BEGIN SELECT @y=i.КодТовара, @x=i.Количество FROM Сделка С, inserted i WHERE С.КодТовара=i.КодТовара --и производится изменения количества товара в --таблице Склад UPDATE Склад SET Остаток=остаток+@x WHERE КодТовара=@y END END Пример 14.2. Использования триггера для сбора статистических данных.

Пример 14.3. Создать триггер для обработки операции удаления записи из таблицы Сделка , например, такой команды:

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

CREATE TRIGGER Триггер_del ON Сделка FOR DELETE AS IF @@ROWCOUNT=1 -- удалена одна запись BEGIN DECLARE @y INT,@x INT --определяется код и количество товара из --удаленной из таблицы Склад записи SELECT @y=КодТовара, @x=Количество FROM deleted --в таблице Склад корректируется количество --товара UPDATE Склад SET Остаток=Остаток-@x WHERE КодТовара=@y END Пример 14.3. Триггер для обработки операции удаления записи из таблицы

Пример 14.4. Создать триггер для обработки операции изменения записи в таблице Сделка , например, такой командой:

во всех сделках с товаром, имеющим код, равный 3, уменьшить количество товара на 10 единиц.

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

CREATE TRIGGER Триггер_upd ON Сделка FOR UPDATE AS DECLARE @x INT, @x_old INT, @y INT, @y_old INT -- курсор с новыми значениями DECLARE CUR1 CURSOR FOR SELECT КодТовара,Количество FROM inserted -- курсор со старыми значениями DECLARE CUR2 CURSOR FOR SELECT КодТовара,Количество FROM deleted OPEN CUR1 OPEN CUR2 -- перемещаемся параллельно по обоим курсорам FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN --для старого кода товара уменьшается его --количество на складе UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old --для нового кода товара, если такого товара --еще нет на складе, вводится новая запись IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара,Остаток) VALUES (@x,@y) ELSE --иначе для нового кода товара увеличивается --его количество на складе UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Пример 14.4. триггер для обработки операции изменения записи в таблице

В рассмотренном триггере отсутствует сравнение количества товара при изменении записи о сделке с его остатком на складе.

Пример 14.5. Исправим этот недостаток. Для генерирования сообщения об ошибке используем в теле триггера команду MS SQL Server RAISERROR , аргументами которой являются текст сообщения, уровень серьезности и статус ошибки.

ALTER TRIGGER Триггер_upd ON Сделка FOR UPDATE AS DECLARE @x INT, @x_old INT, @y INT, @y_old INT ,@o INT DECLARE CUR1 CURSOR FOR SELECT КодТовара,Количество FROM inserted DECLARE CUR2 CURSOR FOR SELECT КодТовара,Количество FROM deleted OPEN CUR1 OPEN CUR2 FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN SELECT @o=остаток FROM Склад WHERE кодтовара=@x IF @o<-@y BEGIN RAISERROR("откат",16,10) CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR22 ROLLBACK TRAN RETURN END UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара,Остаток) VALUES (@x,@y) ELSE UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Пример 14.5. Исправленный вариант триггера для обработки операции изменения записи в таблице

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

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

ALTER TRIGGER Триггер_upd ON Сделка INSTEAD OF UPDATE AS DECLARE @k INT, @k_old INT DECLARE @x INT, @x_old INT, @y INT DECLARE @y_old INT ,@o INT DECLARE CUR1 CURSOR FOR SELECT КодСделки, КодТовара,Количество FROM inserted DECLARE CUR2 CURSOR FOR SELECT КодСделки, КодТовара,Количество FROM deleted OPEN CUR1 OPEN CUR2 FETCH NEXT FROM CUR1 INTO @k,@x, @y FETCH NEXT FROM CUR2 INTO @k_old,@x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN SELECT @o=остаток FROM Склад WHERE КодТовара=@x IF @o>=-@y BEGIN RAISERROR("изменение",16,10) UPDATE Сделка SET количество=@y, КодТовара=@x WHERE КодСделки=@k UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара, Остаток) VALUES (@x,@y) ELSE UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x END ELSE RAISERROR("запись не изменена",16,10) FETCH NEXT FROM CUR1 INTO @k,@x, @y FETCH NEXT FROM CUR2 INTO @k_old,@x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Пример 14.6. Триггер, позволяющий отменять изменение только некоторых записей и выполнять изменение остальных.

Триггеры sql представляют собой специальный тип хранимых процедур, запускаемых сервером автоматически при изменении данных (DML) в таблице, с которой он связан. Триггеры подключаются к определенной таблице. Все производимые триггером изменения данных рассматриваются как одна транзакция.

В отличие от обычной хранимой процедуры/функции, триггер вызывается сервером неявно при возникновения определенного триггерного события. Кроме этого триггер SQL не имеет аргументов. С помощью триггера решаются следующие задачи:

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

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

Применение SQL триггеров связано с дополнительными затратами ресурсов сервера на операции добавления (trigger insert ), обновления (trigger update ) или удаления (trigger delete ) данных в таблице.

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

CREATE TRIGGER

Основной формат команды CREATE TRIGGER показан ниже:

CREATE TRIGGER trigger_name [ BEFORE | AFTER ] ON table_name begin end;

Момент запуска триггера определяется ключевыми словами BEFORE (триггер запускается перед выполнением связанного с ним событием; например, до добавления записи) или AFTER (после события). Если триггер вызывается до события, он может внести изменения в модифицируемую событием запись, если событие - не удаление записи. Некоторые СУБД накладывают ограничения на операторы, которые могут быть использованы в триггере (например, может быть запрещено вносить изменения в таблицу, с которой «связан» триггер, и т.п.).

Триггеры могут быть подключены не к таблице, а к представлению VIEW . В этом случае с их помощью реализуется механизм «обновляемого представления». При подключении триггера к представлению ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит.

Триггер может быть вызван для каждой строки (FOR EACH ROW ), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT ).

Обозначение <список_псевдонимов> относится к таким компонентам, как старая или новая строка (OLD / NEW) либо старая или новая таблица (OLD TABLE / NEW TABLE). Cтарые значения не применимы для событий вставки, а новые – для событий удаления.

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

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

MS SQL trigger

Синтаксис создания триггера в СУБД MS SQL имеет следующий вид:

CREATE TRIGGER trigger_name ON {WITH ENCRYPTION} [ [,] [,] ] [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement }

schema_name

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

trigger_name

Наименование триггера. Аргумент trigger_name должен соответствовать правилам для идентификаторов - за исключением того, что trigger_name не может начинаться с символов # или ##.

table_name | view_name

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

Пример ms sql trigger

Для реализации триггера будут созданы две таблицы: test_table, test_log. К таблице test_table будет подключен триггер. При обновлении записей в таблице test_table триггер будет регистрировать в таблице test_log результаты изменений. Т.е. триггер будет вызываться по событию update.

Тестовая таблица test_table:

Create table dbo.test_table (id int not null, field1 varchar(255) null, field2 varchar(255) null, constraint pkTestTableID primary key (id));

Таблица журналирования test_log:

Create table dbo.test_log (id bigint identity(1,1) not null, table_name varchar(50) not null, oper varchar(15) not null, record_old xml null, record_new xml null, data datetime null, constraint pkTestLogID primary key (id));

Триггер обновления данных:

Trigger update create trigger dbo.trg_test_table_update on dbo.test_table for UPDATE as begin set nocount on -- переменные для хранения старых и новых данных declare @record_new xml; declare @record_old xml; -- в таблице deleted хранятся старые/удаленные данные set @record_old = (SELECT * FROM deleted FOR XML RAW, TYPE); -- в таблице inserted хранятся измененные (только что созданные) данные set @record_new = (SELECT * FROM inserted FOR XML RAW, TYPE); if (@record_new is not null) and (@record_old is not null) begin insert into dbo.test_log (table_name, oper, record_old, record_new, data) values ("test_table", "update", @record_old, @record_new, GETDATE()) end; end;

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

Insert into dbo.test_table (id, field1, field2) values (1, "Кофе", "Nescafe"); insert into dbo.test_table (id, field1, field2) values (2, "Чай" , "Greenfield");

Проверяем работу триггера обновлением строк:

Проверяем таблицу журналирования test_log. Результат должен выглядеть так, как это представлено на скриншоте:

XML данные просматриваются и показывают, что таблица журналирования включает как старые, так и новые значения.

PostgreSQL trigger

Синтаксис создания триггера

CREATE TRIGGER trigger_name [ событие [ OR событие ]] ON table_name FOR EACH { ROW | STATEMENT } EXECUTE PROCEDURE function_name (аргументы)

В аргументе указывается наименование создаваемого триггера. При необходимости может быть указано наименование схемы.

{ BEFORE | AFTER }

Ключевое слово BEFORE означает, что trigger before и функция должна выполняться перед выполнением соответствующего события. Ключевое слово AFTER означает, что trigger after и функция вызывается после завершения операции, приводящей в действие триггер.

{ событие [ OR событие... ] }

В PostgreSQL поддерживаются следующие события . При перечислении нескольких событий в качестве разделителя используется ключевое слово OR.

Наименование таблицы, модификация которой приводит к срабатыванию триггера.

FOR EACH { ROW | STATEMENT }

Ключевое слово, следующее за конструкцией FOR EACH и определяющее количество вызовов функции при наступлении указанного события. Использование ROW означает, что функция вызывается для каждой модифицируемой записи. Если функция должна вызываться всего один раз для всей команды, используется ключевое слово STATEMENT.

EXECUTE PROCEDURE function_name

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

Синтаксис определения триггерной функции

CREATE FUNCTION function_name () RETURNS trigger AS DECLARE -- объявления переменных BEGIN -- тело триггерной функции END; LANGUAGE plpgsql;

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

Наименование	Тип	Описание
NEW	RECORD	Новые значения полей записи, созданной командой INSERT или обновленной командой UPDATE, при срабатывании триггера уровня записи (ROW). Переменная используется для модификации новых записей. Переменная NEW доступна только при INSERT и UPDATE. Поля записи NEW могут быть изменены триггером.
OLD	RECORD	Старые значения полей записи, содержавшиеся в записи перед выполнением команды DELETE или UPDATE при срабатывании триггера уровня записи (ROW). Переменная OLD доступна только при DELETE и UPDATE. Поля записи OLD можно использовать только для чтения, изменять нельзя.
TG_NAME	name	Имя сработавшего триггера.
TG_WHEN	text	Операторы BEFORE или AFTER в зависимости от момента срабатывания триггера, указанного в определении.
TG_LEVEL	text	Строка ROW или STATEMENT в зависимости от уровня триггера, указанного в определении.
TG_OP	text	Строка INSERT, UPDATE или DELETE в зависимости от операции, вызвавшей срабатывание триггера.
TG_RELID	oid	Идентификатор объекта таблицы, в которой сработал триггер.
TG_RELNAME	name	Имя таблицы, в которой сработал триггер.

К отдельным полям записи NEW и OLD в триггерных процедурах обращаются следующим образом: NEW.names, OLD.rg.

Пример postgresql trigger

В примере реализована простая система логирования пользователей. Она следит за таблицей пользователей и все изменения регистрирует в таблице журналирования. Для примера будем создавать упрощенные таблицы.

Таблица пользователей:

CREATE TABLE "public".users (id int not null, name varchar (64), constraint pkUsersID primary key (id));

Таблица протоколирования

CREATE TABLE "public".logs (text varchar(256), data timestamp without time zone);

Триггерная функция

CREATE OR REPLACE FUNCTION "public".add_to_log() RETURNS TRIGGER AS $$ DECLARE v_action varchar(30); v_user varchar(64); v_retstr varchar(256); BEGIN IF TG_OP = "INSERT" THEN v_user = NEW.name; v_action:= "Add new user "; v_retstr:= v_action || v_user; INSERT INTO "public".logs(text, data) values (v_retstr, NOW()); RETURN NEW; ELSIF TG_OP = "UPDATE" THEN v_user = NEW.name; v_action:= "Update user "; v_retstr:= v_action || v_user; INSERT INTO "public".logs(text, data) values (v_retstr, NOW()); RETURN NEW; ELSIF TG_OP = "DELETE" THEN v_user = OLD.name; v_action:= "Remove user "; v_retstr:= v_action || v_user; INSERT INTO "public".logs(text, data) values (v_retstr, NOW()); RETURN OLD; END IF; END; $$ LANGUAGE plpgsql;

Триггерная функция без входящих параметров возвращает специальный тип TRIGGER . В функции в разделе DECLARE определены 3-и переменные. В теле функции выполняется проверка значения переменной TG_OP (внутренняя переменная триггера). В зависимости от транзакции определяем переменнаю v_user и формируется строка retstr, которая записывается в таблицу logs.

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

Сам триггер описывается на PL/pgSQL как:

Trigger insert & trigger update & trigger delete CREATE TRIGGER trg_user AFTER INSERT OR UPDATE OR DELETE ON "public".users FOR EACH ROW EXECUTE PROCEDURE add_to_log ();

Триггер trg_user будет выполняться после выполнения транзакций INSERT, UPDATE, DELETE для каждой строки и вызывать функцию add_to_log(). Теперь любые действия с таблицей users будут протоколироваться.

Следующие скрипты позволяют проверить работоспособность триггера:

Добавление записи в таблицу пользователей insert into users (id, name) values (1, "Киса Воробьянинов"); -- Обновление записи в таблице пользователей update users set name = "Остап Бендер" where id = 1 -- Чтение пользователей select * from users -- Чтение журнала протоколирования. Должно быть 2 записи select * from logs

Oracle triggers

Синтаксис CREATE TRIGGER в Oracle имеет следующий вид:

Trigger before CREATE TRIGGER trigger_name BEFORE DELETE OR INSERT OR UPDATE ON table_name REFERENCING <список_псевдонимов> FOR EACH ROW WHEN (new.field_name > 0) DECLARE -- переменные, константы, курсоры и т.п. BEGIN -- блок PL/SQL END;

В тексте создания триггера может быть включено необязательное ограничение триггера, путем определения булевского выражения SQL в фразе WHEN . Выражение в фразе WHEN проверяется для каждой строки, затрагиваемой триггером. Если результат выражения ИСТИНА, то тело триггера исполняется. Если выражение ЛОЖЬ или NULL, то тело триггера не исполняется. Выражение в фразе WHEN должно быть выражением SQL, но не выражением PL/SQL, и не может включать подзапрос.

REFERENCING

Опция REFERENCING может использоваться в теле триггера для того, чтобы избежать конфликтов между корреляционными именами и именами таблиц, в случае, если таблица имеет имя "OLD" или "NEW". Такая ситуация редка и эта опция почти никогда не применяется.

В качестве примера можно рассмотреть таблицу с именем new. Следующее определение CREATE TRIGGER показывает триггер, ассоциированный с таблицей new , который использует опцию REFERENCING , чтобы избежать конфликтов между корреляционными именами и именем таблицы:

Trigger before CREATE TRIGGER trg_dummy BEFORE UPDATE ON new REFERENCING new AS newest FOR EACH ROW BEGIN:newest.field2:= TO_CHAR (:newest.field1); END;

Оператор new переименован в newest с помощью опции REFERENCING , а затем использован в теле триггера.

Условные предикаты

Если триггер может быть вызван на исполнение более чем одним типом предложения DML (например, "INSERT OR DELETE OR UPDATE"), то в теле триггера можно использовать операторы INSERTING , DELETING и UPDATING , для выполнения различных участков кода в зависимости от условия. В коде внутри тела триггера вы можете использовать следующие условия:

IF INSERTING THEN . . . END IF; IF UPDATING THEN . . . END IF;

Первое условие будет выполняться в тех случаях, когда триггер был стартован при вставке строки в таблицу. Второе условие будет выполняться при обновлении строки таблицы.

В операторе UPDATING можно дополнительно использовать условие проверки имени обновляемого столбца. В качестве примера можно рассмотреть следующий код, в котором тело будет исполняться, если предложение UPDATE, возбудившее триггер, обновляет столбец SAL:

IF UPDATING ("SAL") THEN . . . END IF;

Oracle triggers отключение, включение

В Oracle триггер можно временно выключить, если имеет место одно из следующих условий:

• объект, к которому обращается триггер, недоступен;
• необходимо выполнить массовую загрузку данных без вызова триггеров;
• нобходимо загрузить данные в таблицу без вызова триггера.

Триггер по умолчанию включается в момент его создания. Чтобы отключить триггер, необходимо использовать команду ALTER TRIGGER с опцией DISABLE . Чтобы включить триггер, используйте команду ALTER TRIGGER с опцией ENABLE . Можно одновременно отключить все триггеры, ассоциированные с таблицей, с помощью команды ALTER TABLE с опцией DISABLE ALL TRIGGERS .

Отключение триггера ALTER TRIGGER TRG_Orders_INS DISABLE; -- подключение триггера ALTER TRIGGER TRG_Orders_INS ENABLE; -- отключение всех триггеров таблицы ALTER TABLE Orders DISABLE ALL TRIGGERS;

Для включения или отключения триггера с помощью команды ALTER TABLE, необходимо либо быть владельцем таблицы, либо иметь соответствующую привилегию.

Пример oracle trigger

Генератор последовательностей CREATE SEQUENCE seqID; -- таблица пользователей CREATE TABLE users (id int PRIMARY KEY not null, name varchar(50), phone varchar(15), dt date); -- trigger insert определяет идентификатор записи CREATE OR REPLACE TRIGGER trgAutonumber BEFORE INSERT ON users -- trigger before FOR EACH ROW BEGIN select seqID.NEXTVAL into:new.id from dual; END; -- trigger insert определяет дату записи CREATE OR REPLACE TRIGGER trgDate BEFORE INSERT ON users trigger before FOR EACH ROW BEGIN if:old.dt is null then:new.dt:= current_date; end if; END trgDate;

В следующем примере триггер trgDepartmentst_del_cascade выполняет каскадное удаление записей TRIGGER DELETE CASCADE . Триггер, подключенный к таблице departments, реализует ссылочное действие DELETE CASCADE по первичному ключу таблицы deptID:

Trigger after CREATE OR REPLACE TRIGGER trgDepartmentst_del_cascade AFTER DELETE ON departments FOR EACH ROW BEGIN /* После удаления строки из таблицы Departments удалить из таблицы Employees все строки, имеющие такое же значение deptID. */ DELETE FROM employees WHERE employees.deptID = :old.deptID; END;

Примечание: обычно код для DELETE CASCADE объединяют вместе с кодом для UPDATE SET NULL или UPDATE SET DEFAULT, чтобы учесть как обновления, так и удаления в одном триггере.

Продолжаем изучать SQL в частности Transact-SQL , на примере MS Sql 2008 и сегодня речь пойдет о триггерах . Мы рассмотрим, для чего нужны триггеры, основной синтаксис написания триггера, и, конечно же, все это будем делать на основе простого и понятного примера.

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

Определение и назначение триггера

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

• INSERT – добавление записей в таблицу;
• DELETE – удаление записей из таблицы;
• UPDATE – обновление записей таблицы.

С определением разобрались, теперь давайте разберемся, для чего нужны эти триггеры. А нужны они всего лишь для одной цели, выполнение дополнительных действий до или после самой sql инструкции в которой присутствуют вышеперечисленные события. Имеется в виду, Вы сами можете указать, когда выполнять и на какое событие реагировать. Вы можете написать триггер сразу на все события (insert, delete, update) а можете только на одно или на два.

Какие дополнительные действия здесь подразумеваются, в принципе любые, например, на событие insert можно написать триггер и повесить на таблицу, и при добавлении записей, например, отправлять уведомление на почту, или еще пример, который мы разберем чуть ниже, суть которого заключается в сохранении старой и новой записи при обновлении (update) т.е. своего рода вести аудит всех изменений в той или иной таблицы.

Пример создания триггера в MS SQL 2008

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

Мы с Вами уже реализовывали подобную задачу, которую рассматривали в материале Журналирование изменений данных в таблице на Transact-SQL . Но там мы не использовали триггеры, и использовали своего рода импровизированный метод хранения всех изменений, но сегодня как в материале Transact-sql – работа с xml мы будем использовать XML данные именно в триггере.

Примечание! Все действия мы будем выполнять в Management Studio SQL сервера.

И для начала, давайте создадим таблицу, на которую будем вешать триггер:

Код создания таблицы:

CREATE TABLE .( NULL, (255) NULL, (255) NULL) ON GO

И добавим пару строк, которые в дальнейшем мы будем обновлять, я добавил следующие данные, и проверил их наличие с помощью простого запроса select

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

Код создания таблицы audit:

CREATE TABLE .( IDENTITY(1,1) NOT NULL, (50) NOT NULL, (15) NOT NULL, NULL, NULL, (50) NOT NULL, NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON GO

• id – это идентификатор с автоинкрементном;
• table_name – название таблицы, в которой произошло изменение;
• oper – операция, в данном случае у нас туда будет записывать update;
• record_old – xml данные старой строки;
• record_new — xml данные новой строки;
• username – кто изменил;
• date_ch – когда изменили.

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

CREATE TRIGGER название триггера ON таблица на которую вешать

FOR на какие операции (update, insert, delete)

Сюда пишем сами sql инструкции которые необходимо выполнять при срабатывании триггера

Теперь пришло время привести пример кода самого триггера. SQL инструкции я прокомментировал.

CREATE TRIGGER . ON . for UPDATE AS BEGIN SET NOCOUNT on --переменные для хранения старых и новых данных DECLARE @record_new xml DECLARE @record_old xml --задаем значения этим переменным -- в таблице deleted хранятся старые данные, или удаленные SET @record_old=(SELECT * FROM deleted FOR XML RAW, TYPE); -- в таблице inserted хранятся измененные данные, или только что созданные SET @record_new=(SELECT * FROM inserted FOR XML RAW, TYPE); /*--проверяем, действительно ли update обновил хотя бы одну строку, так как может возникнуть ситуация Вы запустили update при этом он не обновил не одной строки, не подошло условие*/ IF @record_new is not null and @record_old is not null BEGIN INSERT INTO dbo.audit (table_name, oper, record_old, record_new, username, date_ch) VALUES ("test_table", "update", @record_old, @record_new, SUSER_NAME(), GETDATE()) END END GO

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

И посмотрим, что у нас появилось в таблице audit.

Как видите, XML данные легко просматриваются, и мы видим, что в старой записи были одни значения, а в новой уже соответственно новые.

Кстати если нажать на эти xml данные можно попасть в отдельное окно, где еще наглядней будет видно, что за данные мы обновили, но для начала давайте попробуем, обновить сразу несколько строк, и посмотрим, как поведет себя наш триггер, запустим простой запрос на обновление:

UPDATE test_table SET pole1 = "обновили" , pole2 = "обновили" WHERE number in(1,2)

И проверяем audit

Как видите у нас добавилась всего одно строка, но мы же обновили две строки, все очень просто все наши данные записались в xml документ, и соответственно в record_old хранятся старые две строки, а в record_new измененные две строки, т.е. сколько бы мы не обновили строк они будут отображены в xml документе. И теперь давайте все-таки нажмем на xml данные и посмотрим что будет.

А здесь отображены все строки, которые мы затронули.

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

Обзор триггеров Триггеры 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 }