триггера :

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

Изучить типы триггеров, которые могут создаваться на сервере MS SQL Server 2000.
Изучить операторы описания триггеров разных типов и ограничения, накладываемые на допустимые операторы внутри тела триггера.
Изучить порядок создания и методы отладки триггеров на сервере MS SQL Server 2000.
Разработать пять триггеров для учебной базы данных «Библиотека», предложенных вашим преподавателем из заданий, описанных в работе.
Подготовить отчет о проделанной работе в электронном виде.

1. Создание триггера

Триггеры это методы, с помощью которых разработчик приложений для MS SQL Server может обеспечить целостность базы данных. Это тип хранимой процедуры, которая активизируется при попытке изменения данных в таблице, для которой определен триггер. SQL Server выполняет эту процедуру при операциях добавления, обновления и удаления (INSERT , UPDATE , DELETE ) в данной таблице. Поскольку триггер применяется после выполнения операции, он представляет собой последнее слово в модификации. Если триггер вызывает ошибку в запросе, SQL Server отказывается от обновления информации и возвращает приложению, выполняющему это действие, сообщение об ошибке. Если для таблицы определен триггер, то при выполнении соответствующей операции обойти его нельзя.

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

В MS SQL SERVER 2000 появился новый вид триггера INSTEAD OF -триггер. Его принципиальное отличие от обычных (AFTER ) триггеров состоит в том, что он выполняется не после выполнения операции вставки, изменения или удаления, а вместо нее.

Наиболее общее применение триггера поддержка целостности в базах данных.

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

Для создания триггера необходимо быть владельцем таблицы, для которой триггер создается, либо входить в роль db_owner или db_ddladmin , либо же являться администратором SQL-сервера, то есть входить в фиксированную роль сервера sysadmins . При добавлении триггера к таблице изменяется тип доступа, отношение к ней других объектов и т. д.

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

CREATE TRIGGER имя_триггера
ON таблица
{
{FOR | AFTER | INSTEAD OF} { [,] [,] }

AS
{IF UPDATE (столбец_i)
[{AND | OR} UPDATE (столбец_j)]
[… n]
| IF (COLUMNS_UPDATED() {побитовый_оператор} битовая_маска)
{оператор_сравнения} битовая_маска_столбца [… n]
}
инструкции_SQL [… n]
}
}

имя_триггера должно соответствовать стандартным соглашениям об именах объектов SQL Server и быть уникальным в базе данных;
таблица название таблицы, для которой создается триггер;
WITH ENCRYPTION эта опция дает разработчикам возможность запретить пользователям читать текст триггера после его загрузки на сервер. Опять же, отметим, что для того чтобы сделать текст триггера действительно невосстановимым, следует после шифрования удалить соответствующие ему строки из таблицы syscomments ;
FOR DELETE , INSERT , UPDATE ключевые слова, определяющие операцию модификации таблицы, при выполнении которой будет активизирован триггер;
WITH APPEND эта опция необходима, только если установленный уровень совместимости не превышает 65 и используется для создания дополнительных триггеров;
NOT FOR REPLICATION показывает, что триггер не активизируется при модификации таблицы в процессе репликации;
AS ключевое слово, задающее начало определения триггера;
инструкции_SQL в T-SQL триггер может содержать любое количество инструкций SQL, если они заключены в операторные скобки BEGIN ... END ;
IF UPDATE (столбец) для операций добавления и обновления данных можно определить дополнительные условия на конкретный столбец таблицы; при указании нескольких столбцов они разделяются логическими операторами;
IF (COLUMNS_UPDATED()) выше было показано, как можно с помощью конструкции IF UPDATE (столбец) определять, какие столбцы затрагиваются изменениями. Если необходимо проверять, изменяется ли какой-то один конкретный столбец, эта конструкция очень удобна. Однако при построении сложного условия, включающего много столбцов, данная конструкция получается слишком громоздкой. Для таких случаев предназначена конструкция IF (COLUMNS_UPDATED()) . Результатом функции COLUMNS_UPDATED() является набор битов, каждый из которых отвечает за один столбец таблицы; младший бит соответствует первому стобцу, старший последнему. Если в операции, вызвавшей срабатывание триггера, была попытка изменить некоторый столбец, то соответствующий бит будет установлен в 1;
побитовый_оператор побитовый оператор, определяющий операцию выделения нужных битов, полученных с помощью COLUMNS_UPDATED() . Обычно используется оператор & ;
битовая_маска в сочетании с побитовым оператором битовая маска позволяет выделить интересующие разработчика биты, то есть определить, изенялись ли в операции, вызвавшей срабатывание триггера, интересующие его столбцы;
оператор_сравнения и битовая_маска_столбца функция COLUMNS_UPDATED() дает набор битов, соответствующий изменяемым столбцам. С помощью битовой маски и побитового оператора над этим набором битов производится преобразование и получается некий промежуточный результат. С помощью оператора сравнения этот промежуточный результат сравнивается с битовой маской столбца. Если результат сравнения истина, то набор инструкций SQL, составляющий тело триггера, будет выполнен, иначе нет.

Пусть таблица имеет следующую структуру:

CREATE table mytable (a int, b int, c int, d int, e int)

Пять столбцов соответствуют пяти битам, из которых младший соответствует столбцу a , старший столбцу e . Пусть операция, приведшая к срабатыванию триггера, изменяет столбцы a , b и e . Тогда функция columns_updated даст значение 10011. Пусть нас не интересует изменение столбцов b и d , но интересует изменение всех остальных столбцов (a , c и e ), то есть маска будет 10101. Напомним, что на момент написания триггера мы не знаем, какие столбцы затронет та или иная операция изменения или вставки, то есть какой результат даст функция columns_updated . Задав побитовый оператор сравнения во время выполнения, получим 10011 & 10101, что даст в результате 10001, что в десятичном представлении составляет 17. Сравнив это значение с помощью оператора сравнения и битовой маски столбца получим ответ удовлетворяет ли операция изменения/вставки требуемым условиям. Так, например, если бизнес-логика требует, чтобы триггер сработал при изменении все интересующих нас столбцов (a , c , e ), то, естественно, параметры битовая_маска и битовая_маска_столбца должны иметь одинаковые значения, а оператором сравнения должен быть знак равенства. Таким образом, для нашего примера вся конструкция будет иметь вид:

IF (columns_updated & 17) = 17

Если же требуется, чтобы изменился хотя бы один из интересующих нас столбцов, конструкция будет такой:

IF (columns_updated & 17) > 0

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

Создавать триггеры можно и с помощью SQL Server Enterprise Manager.

Запустите SQL Server Enterprise Manager.
Щелкните правой кнопкой мыши на таблице, для которой хотите создать триггер, и в контекстном меню выберите команду Task > Manage Triggers . В результате этих действий появится диалоговое окно, в котором можно ввести текст триггера и присвоить ему имя.
После окончания ввода можно выполнить проверку синтаксиса и нажать кнопку OK для сохранения триггера в базе данных.

Ограничения при создании триггеров

Оператор CREATE TRIGGER может применяться только в одной таблице.
Триггер можно создавать только в текущей базе данных, но в нем можно ссылаться на внешние объекты.
В одном операторе создания триггера можно указывать несколько действий, на которые он будет реагировать.
В тексте триггера нельзя использовать следующие инструкции: ALTER DATABASE , ALTER PROCEDURE , ALTER TABLE , CREATE DEFAULT , CREATE PROCEDURE , ALTER TRIGGER , ALTER VIEW , CREATE DATABASE , CREATE RULE , CREATE SCHEMA , CREATE TRIGGER , CREATE VIEW , DISK INIT , DISK RESIZE , DROP DATABASE , DROP DEFAULT , DROP PROCEDURE , DROP RULE , DROP TRIGGER , DROP VIEW , RESOTRE DATABASE , RESTORE LOG , RECONFIGURE , UPDATE STATISTICS .
Любая правильная операция SET работает только в период существования триггера.
Нельзя выполнить триггер, анализируя в столбцах таблиц INSERTED и DELETED состояние большого двоичного объекта (BLOB ), имеющего тип данных text или image , независимо от того, записывается эта процедура в журнал или нет.
Не следует применять инструкции SELECT , возвращающие результирующие наборы из триггера, для приложения-клиента, требующего специального управления результирующими наборами, независимо от того, делается это в хранимой процедуре или нет.
Нельзя создавать INSTEAD OF UPDATE и DELETE триггеры на таблицы, имеющие внешние ключи с установленными опциями каскадного изменения или удаления соответственно.

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

Пример 1. Триггеры вставки и обновления

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

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

CREATE TRIGGER Tri_Ins_Sales
ON Sales
FOR INSERT, UPDATE
AS
/* Объявить необходимые локальные переменные */
DECLARE @nDayOfMonth TINYINT
/* Найти информацию о добавленной записи */
SELECT @nDayOfMonth = DatePart (day, i.ord_date)
FROM Sales s, Inserted i
WHERE s.stor_id = i.stor_id
AND s.ord_num = i.ord_num
AND s.title_id = i.title_id
/* Проверить критерий отказа и в случае необходимости
послать сообщение об ошибке */
IF @nDayOfMonth > 15
BEGIN
/* Примечание: всегда сначала производите откат. Вы можете не знать,
какого рода ошибка обработки произошла, что может вызвать
неоправданно продолжительное время блокировки */
ROLLBACK TRAN
RAISERROR("Выполняются только заказы, поданные в первые
15 дней месяца", 16, 10)
END

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

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

Столбцы таблицы Inserted в точности совпадают со столбцами рабочей таблицы. Сравнение можно выполнить по столбцам, как это сделано в данном примере, где для проверки правильности дат продажи сравниваются столбцы таблицы Sales .

Можно также создать триггеры, выполняющие работу только в случае обновления конкретного столбца. Для принятия решения о продолжении обработки в триггере может быть применена инструкция IF UPDATE :

IF UPDATE(au_lname)
AND (@@ROWCOUNT=1)
BEGIN

END

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

Операция UPDATE задействует обе системные таблицы. В таблице Inserted хранятся новые значения, а в таблице Deleted старые. Поэтому при анализе изменений вы можете использовать обе эти таблицы.

Часто бывает необходимо заменить некоторые значения на неопределенные. Это делается элементарной операцией присваивания, например:

NUM_READER = NULL

Пример 2. Триггеры удаления

Триггеры удаления (delete triggers ) обычно применяются в двух случаях: предотвращение удаления строк, которое может вызвать проблемы с целостностью данных, например строки, используемой в качестве внешнего ключа к другим таблицам, и выполнение каскадных операций удаления дочерних (children ) строк главной (master ) строки. Такой триггер можно использовать для удаления всей информации о заказах из главной строки продаж.

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

В следующем примере использование переменной @@ROWCOUNT позволяет предотвратить удаление более одной строки. Этот триггер выполняется всегда, когда пользователь пытается удалить строку из таблицы Stores . Если информация касается продаж, то триггер препятствует выполнению этого запроса.

CREATE TRIGGER Tri_Del_Stores
ON Stores
FOR DELETE
AS
/* Проверка количества модифицируемых строк и запрещение удаления более одной строки за один раз */
IF @@ ROWCOUNT > 1
BEGIN
ROLLBACK TRAN
RAISERROR ("За один раз можно удалить только одну строку.", 16, 10)
END
/* Объявление временной переменной для сохранения уничтожаемой информации */
DECLARE @ StorID char (4)
/* Получение значения удаляемой строки */
SELECT @StorID = d.stor_id
FROM Stores s, Deleted d
WHERE s.stor_id *= d.stor_id
IF EXISTS (SELECT *
FROM Sales
WHERE stor_id = @storID)
BEGIN
ROLLBACK TRAN
RAISERROR ("Эта информация не может быть удалена, поскольку имеется соответствующая запись в таблице Sales.", 16, 10)
END

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

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

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

Пример 3. INSTEAD OF-триггеры

INSTEAD OF-триггеры отличаются от обычных (AFTER ) триггеров тем, что они выполняются не после выполнения операции, приведшей к его срабатыванию, а вместо нее, cо всеми вытекающими последствиями, например, такими как возможность их использования совместно с ограничениями целостности. Системные таблицы Inserted и Deleted используются в них так же, как и в AFTER -триггерах. Тело триггера может дублировать операцию, которая вызвала его срабатывание, но это не обязательное условие. Другими словами, если мы описываем INSTEAD OF DELETE -триггер, то ничто не мешает нам выполнить в нем операцию DELETE , удаляющую все строки, которые должны были быть удалены в соответствии с вызвавшей триггер операцией, но можно этого и не делать.

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

Таблица Jobs связана отношением 1:M c таблицей Employees , поэтому невозможно удалить работу, если на нее уже назначены сотрудники. Создадим триггер, который при удалении работы будет проверять, назначены ли на нее сотрудники или нет. Если назначены, то работа не будет удаляться. В связи с тем, что имеется ограничение целостности (DRI ), то работа AFTER -триггера совместно с ним невозможна. То есть можно создать такой триггер:

FOR DELETE
AS
IF EXISTS (SELECT * FROM Employee e JOIN Deleted d ON e.job_id=d.job_id)
BEGIN
ROLLBACK TRAN
END

Кстати, отметим, что в отличие от примера 2, этот триггер позволяет удалять сразу несколько строк. Однако такой триггер сможет работать корректно, только если разорвать связь между таблицами Employees и Jobs , чтобы перед выполнением триггера не отрабатывались DRI .

Но можно создать INSTEAD OF -триггер:

CREATE TRIGGER Check_Job ON Jobs
INSTEAD OF DELETE
AS
DELETE FROM Jobs FROM Jobs j JOIN deleted d on d.job_id = j.job_id
WHERE j.job_id NOT IN (SELECT DISTINCT Job_id FROM Employee)

Такой триггер не будет иметь конфликтов с DRI и будет выполняться.

Проверка DRI выполняется сразу при выполнении операции, то есть раньше, чем выполнение AFTER -триггера. При использовании INSTEAD OF -триггера операция по сути не выполняется, а управление передается триггеру, поэтому DRI не будет выполняться.

Как уже было сказано, таблица Inserted содержит добавленные строки, а таблица Deleted удаленные. Нетрудно догадаться, что при выполнении операции изменения будет использована и таблица Inserted , и таблица Deleted . В этом случае старые значеня окажутся в таблице Deleted , а новые в таблице Inserted . Объединяя их по ключевому столбцу (столбцам), нетрудно определить, какие значения были изменены.

3. Использование вложенных триггеров

Триггеры можно встраивать друг в друга. Допускается 32 уровня вложенности. Если операции вложенного триггера нежелательны, SQL Server можно сконфигурировать так, чтобы отключить их.

Примечание: Уровень вложенности триггера можно проверить в любое время, опросив значение, установленное в переменной @@NESTLEVEL . Оно должно находиться а пределах от 0 до 32.

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

Прямую рекурсию можно отключить (и включить) с помощью опции базы данных RECURSIVE_TRIGGERS . Отключить (и включить) косвенную рекурсию, равно как и вложенность триггеров вообще, можно с помощью серверной опции nested triggers . Эта опция определяет возможность вложенности триггеров не для одной конкретной БД, а для всего сервера.

Следует отметить, что INSTEAD OF -триггеры по своей природе не подвержены прямой рекурсии.

При создании триггера SQL Server не может самостоятельно распознать, что некоторая вложенная конструкция вызывает бесконечный цикл. Подобный факт может быть установлен только во время выполнения этого триггера.

Предположим, что таблица Table_A включает триггер trigger_A , который выполняется, когда происходит обновление Table_A . При выполнении trigger_a вызывает обновление таблицы Table_B . Эта таблица включает в себя триггер trigger_b , который выполняется, когда обновляется Table_B , и вызывает обновление таблицы Table_A . Таким образом, если пользователь обновляет любую из этих двух таблиц, два триггера продолжают бесконечно вызывать выполнение друг друга. При возникновении такой ситуации SQL Server закрывает или отменяет выполнение триггера.

Представим, что таблица Sales включает один триггер, а таблица Stores другой. Ниже показано определение двух вложенных триггеров, которые выполняются, если в таблице Sales производится операция удаления:

/* Первый триггер уничтожает строки в таблице Stores,
если уничтожаются строки таблицы Sales */
CREATE TRIGGER Tri_Del_Sales
ON Sales
FOR DELETE
AS

PRINT "Выполняется триггер удаления для таблицы Sales..."
/* Объявление временной переменной для хранения удаляемой информации */
DECLARE @sStorID char(4),@sMsg varchar(40)
/* Получение значения ID удаляемой строки */

FROM Deleted

/* Удаление строки */
SELECT @sMsg = "Магазин " + @sStorID + " удален"
PRINT @sMsg
DELETE FROM Stores
WHERE stor_id = @sStorID
PRINT "Конец выполнения триггера для таблицы Sales"
GO
/* Второй триггер уничтожает строки одной таблицы,
если уничтожаются строки другой */

ON Stores
FOR DELETE
AS
/* Объявление выполняемого триггера */
PRINT "Выполняется триггер удаления для таблицы Stores..."
/* Объявление временной переменной для хранения информации,
уничтожаемой из таблицы */
DECLARE @sStorID char(4), @sMsg varchar (200)
/* Получение уничтожаемого значения */
SELECT TOP 1 @sStorID = stor_id
FROM Deleted
/* Deleted - это вспомогательная таблица, которую SQL Server
использует для хранения уничтоженных записей */
IF @@ROWCOUNT = 0
BEGIN
PRINT "В таблице Stores нет соответствующих строк"
RETURN
END
/* Удаление записи */
SELECT @sMsg = "Удаление скидок, относящихся к магазину " + @sStorID
PRINT @sMsg
DELETE Discounts
WHERE Stor_id = @sStorID
PRINT "Количество удаленных скидок: " + CONVERT(VARCHAR(4), @@ROWCOUNT)
PRINT "Конец выполнения триггера для таблицы Stores"

Если инструкция DELETE выполняется в таблице Sales , как показано в следующем примере, активизируется триггер, что в свою очередь вызывает выполнение триггера таблицы Stores .

Выполним:

DELETE FROM Sales WHERE stor_id = "8042"

Результат:

Выполняется триггер удаления для таблицы Sales...
Магазин 8042 удален
Выполняется триггер удаления для таблицы Stores...
Удаление скидок, относящихся к магазину 8042
(1 row(s) affected)
Количество удаленных скидок: 1
Конец выполнения триггера для таблицы Stores
(1 row(s) affected)
(4 row(s) affected)
Конец выполнения триггера для таблицы Sales

Обратите внимание на порядок выдаваемых сообщений. Сначала запускается триггер для таблицы Sales . Он удаляет строку из таблицы Stores , запуская таким образом для нее триггер. При этом фактически ни из таблицы Sales , ни из таблицы Stroes удаления еще не произошло (удаление в процессе) об этом свидетельствует отсутствие автоматического сообщения сервера (N row(s) affected) , которое появляется при удалении из любой таблицы и показывает, сколько строк было удалено.

После запуска триггер на таблицу Stores удаляет связанные строки из таблицы скидок (Discounts ), о чем выдается сообщение (1 row(s) affected) . Затем он выводит соответствующие сообщения и заканчивает свою работу. Как только он закончил свою работу, происходит удаление строки из таблицы Stores , удаление которой и вызвало его работу. Далее, поскольку эта строка удалена, происходит возврат к работе триггера на таблицу Stores . Этот триггер выдает свое последнее сообщение об окончании работы и завершается. Как только он завершился, выводится сообщение (1 row(s) affected) , свидетельствующее об удалении строки из таблицы Stores . И уже только после этого окончательно удаляются строки из таблицы Sales .

Примечание: Триггеры и механизм декларативной ссылочной целостности обычно не могут работать вместе. Например, в предыдущем примере показано, что перед выполнением инструкции DELETE необходимо сначала удалить условие на значение FOREIGN KEY в таблице Discounts . Везде, где это возможно, следует применять либо триггер, либо условие ссылочной целостности. Однако, как уже говорилось, в MS SQL Server 2000 появились INSTEAD OF -триггеры. Их можно использовать совместно с механизмами декларативной целостности, нельзя только использовать при этом каскадные операции в связях на ту же операцию, на которую создан INSTEAD OF -триггер. Например, если создан INSTEAD OF DELETE -триггер, то нельзя в связях, в которых эта таблица является подчиненной, использовать конструкцию ON DELETE CASCADE .

Пример 2

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

CREATE TRIGGER Del_Empl_Tr ON Employee
FOR DELETE
AS
IF EXISTS (SELECT * FROM Employee e
JOIN Deleted d on e.lname = d.lname OR e.Fname = d.fname)
DELETE FROM Employee
FROM Employee e JOIN Deleted d on e.lname = d.lname OR e.Fname = d.fname

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

Если теперь выполнить инструкцию:

DELETE FROM Employee WHERE Fname = "Иван" AND Lname = "Иванов"

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

Заметьте, что тут обязательно надо делать проверку IF EXISTS . Если ее не сделать, то когда дело дойдет до удаления Петра Васильева, будет выполнена инструкция DELETE и, хотя она фактически никого не удалит, вновь вызванный триггер опять вызовет самого себя (опять никого фактически не удаляя) и т. д., до превышения максимального уровня вложенности 32. После достижения уровня вложенности 32 произойдет ошибка и все действия будут отменены.

Пример 3. Косвенная рекурсия

Изменим пример 1 таким образом, чтобы, если удаляется строка из таблицы Sales , то удалялся бы и магазин, в котором была сделана удаляемая продажа. Поскольку отношение между этими таблицами 1:M, то в удаляемом магазине может быть множество продаж, а не только та, которую мы пытаемся удалять. Поэтому цепочка должна быть следующая: удаляем продажу → удаляется магазин, в котором она была сделана, → удаляются все остальные продажи, сделанные в этом магазине, → удаляются все скидки, привязанные к этому магазину. Кроме того, реализуем эти триггеры в виде INSTEAD OF -триггеров, чтобы не было необходимости разрывать связи между таблицами.

CREATE TRIGGER Tri_Del_Sales
ON Sales
INSTEAD OF DELETE
AS
DELETE FROM Sales FROM Sales s JOIN Deleted d on d.ord_num = s.ord_num
IF EXISTS (SELECT * FROM Stores s JOIN Deleted d ON d.stor_id = s.stor_id)
DELETE FROM Stores FROM Stores s JOIN Deleted d ON d.stor_id = s.stor_id
GO

CREATE TRIGGER Tri_Del_Stores
ON Stores
INSTEAD OF DELETE
AS
DELETE FROM Discounts FROM Discounts di JOIN Deleted de on di.stor_id=de.stor_id
IF EXISTS(SELECT * FROM Sales s JOIN Deleted d on d.stor_id = s.stor_id)
DELETE FROM Sales FROM Sales s JOIN Deleted d on d.stor_id = s.stor_id
DELETE FROM Stores FROM Stores s JOIN Deleted d on d.stor_id = s.stor_id

Для проверки можно выполнить команду:

DELETE FROM Sales WHERE ord_num = "P723"

В результате из таблицы Sales будет удалена не только строка с кодом заказа "P723", но и три другие строки, относящиеся к тому же магазину (код 8042). Также будет удален сам магазин 8042 и относящаяся к нему скидка.

В приведенном примере, кроме всего прочего, удалены все выводы сообщений и изменены вызовы операторов DELETE поскольку выводов сообщений нет, то нет и необходимости формировать значение локальной переменной @sStroID . Использование этой переменной в операторе DELETE несколько ограничивало применимость триггеров. Так, триггеры в примере 2 были рассчитаны на то, что будут удаляться записи только для одного магазина, и при удалении записей, относящихся сразу к нескольким магазинам, работали некорректно. Теперь же такого ограничения нет, поскольку удаляются все записи, связанные с записями в таблице Deleted (то есть со всеми фактически удаляемыми строками).

Можно задать вопрос: зачем использовать рекурсию? Не проще ли было бы при удалении из таблицы Sales удалять в триггере на нее все записи из самой себя, относящиеся к тому же магазину, что и удаляемая строка продажи, после этого удалять строку из таблицы Stores , а в триггере на таблицу Stores удалять связанные записи только из таблицы Discounts ? Да, так можно сделать, но только в том случае, если мы всегда будем давать команду удаления именно из таблицы Sales (как это было сделано выше при проведении проверки). Однако мы можем дать команду удаления и из таблицы Stores , например так:

DELETE FROM stores WHERE stor_id = 8042

И в этом случае мы также хотим, чтобы команда отработала корректно. Если же в триггере на таблицу Stores , как было предложено в вопросе, не будет включено удаление из Sales , то если для удаляемого магазина есть продажи, то такое удаление приведет к ошибке. Наш же пример позволяет решить эту проблему. Ну а если в триггер на Stores включена команда удаления из Sales , то в триггере на Sales нет необходимости включать удаление продаж в том же магазине, что и удаляемый, поскольку это будет автоматически выполнено через рекурсию.

Замечание 1: Чтобы уже созданные в предыдущих примерах триггеры не мешались, надо удалить их с помощью инструкции DROP TRIGGER имя_триггера .

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

Пример 4

В последнем примере рассмотрим случай определения нескольких триггеров для одной операции модификации таблицы:

CREATE TRIGGER trig_del_l ON Authors FOR DELETE AS
PRINT "Триггер удаления №1"
GO

CREATE TRIGGER trig_del_2 ON Authors FOR DELETE AS
PRINT "Триггер удаления №2"
GO

CREATE TRIGGER trig_upd_l ON Authors FOR UPDATE AS
PRINT "Триггер обновления №1"
GO

CREATE TRIGGER trig_upd_3 ON Authors FOR UPDATE AS
PRINT "Триггер обновления №3" "
GO

CREATE TRIGGER trig_upd_2 ON Authors FOR UPDATE AS
PRINT "Триггер обновления №2"
GO

А теперь попробуем изменить какую-либо запись в таблице:

UPDATE Authors
SET au_fname = "Юрий" WHERE au_lname = "Тихомиров";

Cработают все три триггера обновления:

Триггер обновления №1

Триггер обновления №3

Триггер обновления №2

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

Триггер обновления №1

Триггер обновления №2

Триггер обновления №3

Множественные триггеры достаточно активно используются при репликации.

4. Отображение информации о триггере и изменение триггера

Для выяснения назначения триггера таблицы необходимо отобразить информацию, описывающую любой триггер, которым владеет таблица. Существует несколько путей получения информации о триггере конкретной таблицы. Одним из них является SQL Server Enterprise Manager, другим системные процедуры sp_help и sp_depends . Для того чтобы посмотреть текст триггера через Enterprise Manager, выполните следующие действия:

В Enterprise Manager выберите сервер и базу данных, с которой вы хотите работать.
Откройте таблицу в режиме проектирования командой Design Table и в ее окне нажмите кнопку Triggers на панели инструментов.
Появится диалоговое окно создания триггера, где можно посмотреть текст любого из установленных триггеров.

Системные хранимые процедуры sp_help и sp_depends уже были описаны в теме «Хранимые процедуры».

Для того чтобы изменить функциональность триггера, можно либо удалить его и создать новый с соответствующими изменениями, либо изменить уже существующий. Для того чтобы изменить существующий триггер в T-SQL существует команда ALTER TRIGGER . Ее синтаксис аналогичен синтаксису команды CREATE TRIGGER , за исключением ключевого слова ALTER вместо CREATE .

Можно также изменить триггер с помощью Enterprise Manager. Для этого после входа в Enterprise Manager надо просто внести изменения и применить их.

5. Удаление триггеров

Иногда нужно удалить триггеры из таблицы или таблиц. Например, при перемещении приложения в рабочую среду может потребоваться удаление триггеров, обеспечивавших высокое качество обработки, но сильно уменьшавших производительность. Можно просто удалить триггеры для замены их на более новую версию. Для удаления триггера применяется инструкция DROP TRIGGER :

DROP TRIGGER [владелец.]имя_ триггера [, n]

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

Пример удаления триггера Tri_Dei_Autnors :

DROP TRIGGER Tri_Del_Authors

6. Приостановка и возобновление работы триггеров

Часто бывает необходимо отключить на некоторое время работу триггера без его фактического удаления. Этого можно достигнуть используя конструкцию ALTER TABLE <имя_таблицы> DISABLE TRIGGER <имя триггера> для отключения триггера и ALTER TABLE <имя_таблицы> ENABLE TRIGGER <имя триггера> для возобновления его работы.

Задания для самостоятельной работы

Перед началом выполнения заданий напомним, что триггеры это системные хранимые процедуры, которые связаны с конкретной таблицей. Для вызова редактора триггеров необходимо выделить таблицу, по правой кнопке контекстного меню перейти в раздел Все задачи > Manage triggers , и вы попадаете в редактор триггеров (рис. 1).

Рис. 1. Начальное состояние редактора триггеров при создании нового триггера

Задание 1. Разработать триггер, который удалял бы запись о книге в том случае, если удаляется последний экземпляр данной книги. Для какой таблицы вы будете писать этот триггер? При написании триггера помните, что с таблицей «Книги» у нас связаны таблицы «Авторы» и «Системный каталог». Однако они связаны отношением «многие-ко-многим», для чего используются связующие таблицы. Удалить данные о книге нельзя, если на нее есть ссылки в этих связующих таблицах. Предусмотрите предварительное удаление данных из связующих таблиц. Проверьте работу данного триггера.

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

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

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

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

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

Задание 4. Разработать триггер, который добавлял бы один экземпляр при вводе новой книги. Действительно, мы определили, что книги у нас в каталоге присутствуют только в том случае, если они есть в нашей библиотеке, поэтому при вводе новой книги в таблицу «Экземпляр» должен добавляться один экземпляр данной книги.

Задание 5. Разработать триггер типа INSTEAD OF для таблицы «Читатели». Данный триггер должен проверять, есть ли информация хотя бы об одном из телефонов для оперативной связи с читателем, и если такой информации нет, то не вводить данные о читателе.

Задание 6. Разработать триггер, который при изменении значения поля, символизирующего присутствие экземпляра книги в библиотеке, например YES_NO , с "1" на "0" автоматически заменял бы значения в полях «Дата выдачи», «Дата возврата» и «Номер читательского билета» на неопределенное.

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

Задание 8. Разработать триггер, который при удалении экземпляра книги проверял бы, сколько экземпляров данной книги осталось в библиотеке, и если остался только один экземпляр, то повышал бы стоимость данной книги на 15 % как редкой и ценной.

Версия для печати

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

Определение триггера в стандарте языка 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 .

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

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

В 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 .

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

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

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

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

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

команда 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. Триггер, позволяющий отменять изменение только некоторых записей и выполнять изменение остальных.

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

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

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

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

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

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

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

3.4.1. Создание триггера

Для создания триггеров используйте оператор CREATE TRIGGER. В операторе указывается таблица, для которой объявляется триггер, событие, для которого триггер выполняется и индивидуальные инструкции для триггера. В общем команда показана в листинге 3.2.

Листинг 3.2. Общий вид команды CREATE TRIGGER

CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ { IF UPDATE (column) [ { AND | OR } UPDATE (column) ] [ ...n ] | IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask) { comparison_operator } column_bitmask [ ...n ] } ] sql_statement [ ...n ] } }

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

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

ALTER DATABASE;
CREATE DATABASE;
DISK INIT;
DISK RESIZE;
DROP DATABASE;
LOAD DATABASE;
LOAD LOG;
RECONFIGURE;
RESTORE DATABASE;
RESTORE LOG.

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

3.4.2. Откат изменений в триггере

Объявление триггера может содержать оператор ROLLBACK TRANSACTION даже если не существует соответствующего BEGIN TRANSACTION. Как мы уже говорили, для любого изменения SQL сервер требует транзакции. Если она не указано явно, то создается неявная транзакция. Если выполняется оператор ROLLBACK TRANSACTION, то все изменения в триггере и изменения, которые стали причиной срабатывания триггера - откатываются.

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

Если срабатывает оператор ROLLBACK TRANSACTION, содержимое транзакции откатывается. Если есть операторы, следующие за ROLLBACK TRANSACTION, операторы выполняются. Это может быть не обязательным при использовании команды RETURN;
Если триггер откатывает транзакцию, определенную пользователем, то она откатывается полностью. Если триггер сработал, на выполнение модуля, для модуля команды также отменяются. Последующие операторы модуля не выполняются;
Вы должны минимизировать использование ROLLBACK TRANSACTION в коде триггера. Откат транзакции создает дополнительную работу, потому что все работы, которые не были закончены на данный момент в транзакции, будут незавершенными. Это будет негативно сказываться на производительности. Запускайте транзакцию после того, как все проверено, чтобы не пришлось ничего откатывать в триггере.

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

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS ROLLBACK TRANSACTION

Как всегда, я разбил все действия на строки, чтобы их лучше было видно и легче было читать и изучать тему. В первой строке, после оператора CREATE TRIGGER стоит название. При именовании триггеров я следую следующему правилу:

имя начинается одной или сочетания букв u (update или обновление), i (insert или вставка) или d (delete или удаление). По этим буквам вы легко можете определить, на какие действия срабатывает триггер;
после подчеркивания идет имя таблицы, для которого создается триггер.

После имени идет ключевое слово ON и имя таблицы, для которой создается триггер.

Во второй строке идет ключевое слово FOR и событие, на которое срабатывает триггер. В данном примере указано действие UPDATE, т.е. обновление. И, наконец, после ключевого слова AS идет тело триггера, т.е. команды, которые должны выполняться. В данном примере выполняется только одна команда - ROLLBACK TRANSACTION, т.е. откат.

Теперь попробуем изменить данные в таблице tbPeoples, чтобы сработал триггер:

UPDATE tbPeoples SET vcFamil="dsfg"

В данном примере мы пытаемся изменить содержимое поля "vcFamil" для всех записей таблицы tbPeoples. Почему пытаемся? Да потому что при изменении срабатывает триггер с откатом транзакции. Выполните выборку данных, чтобы убедиться, что все данные на месте и не изменились:

SELECT * FROM tbPeoples

Не смотря на то, что при обновлении данных мы не запускали транзакцию, оператор ROLLBACK TRANSACTION был выполнен без ошибок, и изменения отменились.

3.4.3. Изменение триггера

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

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

Листинг 3.3. Оператор обновления триггера

ALTER TRIGGER trigger_name ON (table | view) [ WITH ENCRYPTION ] { { (FOR | AFTER | INSTEAD OF) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } [ NOT FOR REPLICATION ] AS sql_statement [ ...n ] } | { (FOR | AFTER | INSTEAD OF) { [ INSERT ] [ , ] [ UPDATE ] } [ NOT FOR REPLICATION ] AS { IF UPDATE (column) [ { AND | OR } UPDATE (column) ] [ ...n ] |IF(COLUMNS_UPDATED() { bitwise_operator } updated_bitmask) { comparison_operator } column_bitmask [ ...n ] } sql_statement [ ...n ] } }

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

ALTER TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE, INSERT AS ROLLBACK TRANSACTION

Как видите, оператор обновления похож на создание триггера. Разница в том, что в первой строке стоит оператор ALTER TRIGGER. Во второй строке произошло изменение, и теперь триггер будет срабатывать не только на обновление (UPDATE), но и на добавление (INSERT).

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

INSERT INTO tbPeoples(vcFamil) VALUES("ПЕТЕЧКИН")

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

ALTER TABLE table {ENABLE | DISABLE} TRIGGER {ALL | trigger_name [,..n]}

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

ALTER TABLE tbPeoples DISABLE TRIGGER u_tbPeoples

В первой строке мы пишем оператор ALTER TABLE и имя изменяемой таблицы. Во второй строке нужно указать ключевое слово DISABLE (отключить) или ENABLE (включить) и ключевое слово TRIGGER. И, наконец, имя триггера.

Попробуйте теперь добавить запить в таблицу tbPeoples. На этот раз, все пройдет успешно.

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

ALTER TABLE tbPeoples ENABLE TRIGGER ALL

3.4.4. Удаление триггеров

Для удаления триггера вы можете воспользоваться оператором DROP TRIGGER. Он удаляется автоматически, когда связанная с ним таблица удаляется.

Пример удаления триггера:

DROP TRIGGER u_tbPeoples

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

3.4.5. Как работают триггеры?

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

Триггер INSERT

Что происходит, когда срабатывает триггер добавления записей? Давайте рассмотрим выполняемые сервером шаги:

Пользователем выполняется оператор INSERT для добавления записей;
Вызывается триггер;

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

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

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

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

Листинг 3.4. Использование таблицы inserted

CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples FOR INSERT AS DECLARE @Name varchar(50) SELECT @Name=vcName FROM inserted IF @Name="ВАСЯ" BEGIN PRINT "ОШИБКА" ROLLBACK TRANSACTION END

В данном примере мы создаем триггер на добавление записей. Внутри триггера мы объявляем переменную @Name типа varchar длиной в 50 символов. В эту переменную мы сохраняем содержимое поля "vcName" таблицы inserted. Далее проверяем, если имя равно Вася, то сообщаем об ошибке и откатываем транзакцию. Иначе, строка будет удачно добавлена.

Давайте для закрепления материала, напишем триггер, который запретит нулевые значения для поля "vcName". Код такого триггера можно увидеть в листинге 3.5.

CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples FOR INSERT AS IF EXISTS (SELECT * FROM inserted WHERE vcName is NULL) BEGIN PRINT "ОШИБКА, вы должны заполнить поле vcName" ROLLBACK TRANSACTION END

В этом примере мы проверяем, если в таблице inserted есть записи с нулевым значением поля "vcName", то откатываем попытку добавления.

Триггер DELETE

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

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

Удаляемые строки помещаются в таблицу deleted, с помощью которой вы можете увидеть удаляемые строки. Это логическая таблицf, которая ссылается на данные журнала оператора DELETE.

Вы должны учитывать:

когда строки добавляются в таблицу deleted, они еще существуют в таблице базы данных;
для таблицы deleted выделяется память, поэтому она всегда в кэше;
триггер удаления не выполняется на операцию TRUNCATE TABLE (очистка таблицы) потому что эта операция не заносится в журнал и не удаляет строк.

Давайте попробуем создать триггер, который запретит удаление пользователя с определенным именем. Пример такого триггера можно увидеть в листинге 3.6.

Листинг 3.6. Пример запрета удаления с помощью триггера

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF EXISTS (SELECT * FROM deleted WHERE vcName="рлр") BEGIN PRINT "ОШИБКА, нельзя удалить этого пользователя" ROLLBACK TRANSACTION END

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

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

DELETE FROM tbPeoples WHERE vcName="рлр" or vcName="ВАСИЛИЙ"

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

Посмотрим на еще один пример в котором запрещается удаление генерального директора. Без триггера такое сделать невозможно:

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF EXISTS (SELECT * FROM deleted WHERE idPosition=1) BEGIN PRINT "ОШИБКА, нельзя удалить этого пользователя" ROLLBACK TRANSACTION END

В этом примере, запрещается удаление записи, если поле "idPosition" равно 1. Попробуйте удалить такую запись:

DELETE FROM tbPeoples WHERE idPosition=1

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

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

Триггер UPDATE

Обновление происходит в два этапа – удаление и вставка. Нет, физически в базе данных происходит изменение, это триггер видит два этапа. Поэтому существующие строки помещаются в таблицу deleted (то есть то, что было), а новые данные помещаются в таблицу inserted. Триггер может проверять эти таблицы для определения, какие строки и как могут измениться.

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

Давайте создадим триггер на таблицу tbPeoples, который будет выводить на экран сообщение, если изменяется поле "vcName"

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS IF UPDATE (vcName) PRINT "Я надеюсь, что вы правильно указали имя"

После оператора IF UPDATE, в скобках указано поле, которое необходимо проверить, было ли оно изменено. Если да, то будет выполнен следующий за проверкой оператор. В данном случае, это вывод на экран сообщения с помощью PRINT. Когда указанное поле не изменяется, то оператор конечно же не выполняется. Если нужно выполнить несколько операторов, то объедините их с помощью BEGIN и END.

Следующий запрос тестирует триггер:

UPDATE tbPeoples SET vcName="ИВАНУШКА" WHERE vcFamil="ПОЧЕЧКИН"

Убедитесь, что сообщение из триггера выводится на экран.

Давайте с помощью триггера попробуем запретить изменение полей, составляющих ФИО ("vcFamil", "vcName" и "vcSurName"). Для этого, если изменено одно из этих полей, то выводим на экран сообщение о запрете и откатываем транзакцию:

CREATE TRIGGER u_tbPeoples ON dbo.tbPeoples FOR UPDATE AS IF UPDATE (vcName) OR UPDATE (vcFamil) OR UPDATE (vcSurname) BEGIN PRINT "Нельзя изменять фамилию, имя и отчество" ROLLBACK TRANSACTION END

С помощью такого запроса легко увидеть, как проверять обновление сразу нескольких полей и выводить несколько операторов. Обратите внимание, что проверку делает именно оператор UPDATE, а не IF UPDATE. Я даже не знаю, почему разработчики SQL Server объединяют эти два оператора. Первый, это логический оператор, а второй – проверка, было ли обновлено поле.

3.4.6. INSTEAD OF

Вы можете указать триггер INSTEAD OF для таблиц и просмотрщиков. Действия такого триггера выполняются вместо операторов, сгенерировавших триггер. Не понятно? Рассмотрим пример. Допустим, что у вас есть триггер INSTEAD OF на событие обновления таблицы. Если пользователь выполняет обновление, то выполняется триггер, но при этом, оператор, запущенный пользователем, только генерирует событие. Реальное обновление данных должно происходить с помощью операторов триггера.

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

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

Давайте создадим объект просмотра, который будет выбирать фамилию работника и название должности. Назовем этот объект просмотра Peoples:

CREATE VIEW People AS SELECT vcFamil, vcPositionName FROM tbPosition ps, tbPeoples pl WHERE ps.idPosition=pl.idPosition

Теперь создадим триггер INSTEAD OF на этот объект просмотра, с помощью которого, можно будет добавлять записи и при этом, они корректно будут прописываться, каждая в свою таблицу:

Листинг 3.7. Триггер INSTEAD OF для вставки данных

CREATE TRIGGER i_People ON dbo.People INSTEAD OF INSERT AS BEGIN -- Добавление должности INSERT INTO tbPosition (vcPositionName) SELECT vcPositionName FROM inserted i -- Добавление работника INSERT INTO tbPeoples (vcFamil, idPosition) SELECT vcFamil, idPosition FROM inserted i,tbPosition pn WHERE i.vcPositionName=pn.vcPositionName END

В этом примере интересности начинаются прямо со второй строки. Здесь указывается оператор INSTEAD OF и событие, на которое нужно реагировать. В данном случае в качестве события выступает вставка (INSERT).

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

INSERT INTO tbPeoples (vcFamil, idPosition) SELECT vcFamil, idPosition FROM inserted i,tbPosition pn WHERE i.vcPositionName=pn.vcPositionName

Попробуйте выполнить следующий запрос на добавление записей в объект просмотра:

INSERT INTO People VALUES("ИВАНУШКИН", "Клерк")

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

SELECT * FROM People

При обновлении таблицы есть одна проблема – нужно связать обновляемые данные с существующими. Первым на ум приходит запрос типа:

UPDATE tbPosition SET vcPositionName=i.vcPositionName FROM tbPosition pn, inserted i WHERE i.vcPositionName = pn.vcPositionName

Здесь мы связываем таблицу должностей с таблицей inserted. Но такой запрос никогда не будет выполнен. Почему? В inserted находятся новые значения, а в tbPosition еще старые и названия должностей никогда не свяжутся. Если связать с таблицей deleted, то записи свяжутся, но мы не будем знать новых значений, которые нужно занести в таблицу. Проблему можно решить, но лучшим вариантом будет добавление в объект просмотра ключевых полей:

ALTER VIEW People AS SELECT idPeoples, pl.idPosition, vcFamil, vcPositionName FROM tbPosition ps, tbPeoples pl WHERE ps.idPosition=pl.idPosition

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

Листинг 3.8. Обновление связанной вьюшки с помощью триггера

CREATE TRIGGER u_People ON dbo.People INSTEAD OF UPDATE AS BEGIN UPDATE tbPosition SET vcPositionName=i.vcPositionName FROM tbPosition pn, inserted i WHERE i.idPosition=pn.idPosition UPDATE tbPeoples SET vcFamil=i.vcFamil FROM tbPeoples pl, inserted i WHERE i.idPeoples=pl.idPeoples END

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

UPDATE People SET vcFamil="ИВАНУШКИН", vcPositionName="Генеральный директор" WHERE idPeoples=40 AND idPosition=13

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

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

3.4.7. Дополнительно о триггерах

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

Выполнения действий или каскадного обновления или удаления. Целостность ссылок может отличаться при использовании ограничений FOREIGN KEY и REFERENCE в операторе CREATE TABLE. Но триггер выгоден для гарантирования необходимых действий, когда должны быть произведены каскадные удаления или обновления, потому что триггеры более мощные. Если ограничение существует для таблицы с триггером, оно проверяется до выполнения триггера. Если ограничение нарушено, то триггер не работает. Если ограничение не сработает, то с помощью триггера можно реализовать более сложные проверки, которые уж точно будут гарантировать, что данные не нарушат целостность и пользователь внесет только те данные, которые разрешены;
Вы должны учитывать, что в таблицу может вставляться сразу несколько строк. Вы должны учитывать это при написании триггеров, как мы это делали при создании примеров с использованием INSTEAD OF;
Ограничения, правила и значения по умолчанию могут генерировать только стандартные системные ошибки. Если вам нужны собственные сообщения, вы должны использовать триггеры.

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

CREATE TRIGGER iu_tbPeoples ON dbo.tbPeoples FOR INSERT, UPDATE AS Действие

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

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

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

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

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

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

3.4.8. Практика использования триггеров

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

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

Итак, давайте создадим триггер, который при изменении или удалении строк в таблице tbPeoples будет копировать их в таблицу истории tbpeoplesHistory. Если бы первичный ключ был в виде уникального идентификатора, то задача решалась бы следующим образом:

CREATE TRIGGER ud_tbPeoples ON dbo. tbPeoples FOR UPDATE, DELETE AS INSERT INTO tbPeoplesHistory SELECT newid(), del.* FROM Deleted del

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

В данном примере содержимое таблицы Deleted копируется в таблице tbPeoplesHistory. Запрос упрощается тем, что первичный ключ можно сгенерировать с помощью функции newid().

Но в нашей задаче первичный ключ автоматически увеличиваемый и его нельзя генерировать. Придется перечислять все поля:

CREATE TRIGGER ud_tbPeoplesHistory ON dbo.tbPeoples FOR UPDATE, DELETE AS INSERT INTO tbPeoplesHistory (idPeoples, vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) SELECT del.* FROM Deleted del

Теперь посмотрим, как можно запретить удаление более чем одной строки:

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS IF (SELECT count(*) FROM deleted)>1 BEGIN PRINT "Нельзя удалять более одной строки" ROLLBACK TRANSACTION END

Любой триггер может содержать операторы UPDATE, INSERT или DELETE, которые воздействуют на другие таблицы, как это происходило в примере создания истории изменений. С включенным вложением, триггер, который изменяет таблицу, может активировать (за счет выполнения операции изменения другой таблицы, на которую есть свой триггер) другой триггер, который по очереди может активировать третий и так далее.

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

Sp_configure ‘nested triggers’, 0

Триггеры могут иметь вложения до 32 уровней. Если какой-нибудь триггер зациклится, то будет превышен предел. Триггер прерывается и транзакция откатывается.

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

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

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

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

ALTER DATABASE FlenovSQLBook SET RECURSIVE_TRIGGERS ON

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

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

CREATE TRIGGER d_tbPeoples ON dbo.tbPeoples FOR DELETE AS DELETE pn FROM tbPhoneNumbers pn, inserted i WHERE pn.idPeoples=i.idPeoples

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

При добавлении записи в таблицу телефонов увеличиваем значение поля в таблицы работников;
При удалении номера телефона, уменьшаем значения поля.

Попробуйте реализовать это самостоятельно, чтобы закрепить знания и потренироваться в работе с SQL запросами.

Для определения таблиц с триггером, выполните процедуру sp_depends. Например, выполните следующую команду, чтобы увидеть все зависимости для таблицы tbPeoples:

EXEC sp_depends "tbPeoples"

Для определения, какие триггеры существуют на определенную таблицу, и на какие действия выполните процедуру sp_helptrigger. Следующий пример отображает все триггеры, которые принадлежат объекту просмотра People (если нужно просмотреть триггеры таблицы, то укажите ее имя):

EXEC sp_helptrigger People

Для просмотра кода существующего триггера используйте sp_helptext. Например, следующая команда позволяет увидеть текст триггера u_People, которую мы создавали для объекта просмотра.

ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2008)База данных SQL AzureХранилище данных SQL AzureParallel Data Warehouse

Создает триггер языка обработки данных, DDL или входа. Триггер - это особая разновидность хранимой процедуры, выполняемая автоматически при возникновении события на сервере базы данных. Триггеры языка обработки данных выполняются по событиям, вызванным попыткой пользователя изменить данные с помощью языка обработки данных. Событиями DML являются процедуры INSERT, UPDATE или DELETE, применяемые к таблице или представлению. Эти триггеры срабатывают при запуске любого допустимого события независимо от того, влияет ли оно на какие-либо строки таблицы.

Триггеры DDL срабатывают в ответ на ряд событий языка описания данных (DDL). Эти события прежде всего соответствуют инструкциям Transact-SQL CREATE, ALTER, DROP и некоторым системным хранимым процедурам, которые выполняют схожие с DDL операции. Триггеры входа могут срабатывать в ответ на событие LOGON, возникающее при установке пользовательских сеансов. Триггеры могут быть созданы непосредственно из Transact-SQL инструкций или методов сборок, созданных в Microsoft .NET Framework общеязыковая среда выполнения (CLR) и передачи в экземпляр SQL Server. SQL Server позволяет создавать несколько триггеров для любой инструкции.

SQL Server Syntax -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name ON { table | view } [ WITH [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= assembly_name.class_name.method_name

SQL Server Syntax -- Trigger on an INSERT, UPDATE , or DELETE statement to a table (DML Trigger on memory -optimized tables) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON { table } [ WITH [ ,...n ] ] { FOR | AFTER } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement [ ; ] [ ,...n ] } ::= [ NATIVE_COMPILATION ] [ SCHEMABINDING ] [ EXECUTE AS Clause ]

Trigger on a CREATE , ALTER , DROP , GRANT , DENY , REVOKE or UPDATE statement (DDL Trigger ) CREATE [ OR ALTER ] TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

Trigger on a LOGON event (Logon Trigger ) CREATE [ OR ALTER ] TRIGGER trigger_name ON ALL SERVER [ WITH [ ,...n ] ] { FOR | AFTER } LOGON AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]

Windows Azure SQL Database Syntax -- Trigger on an INSERT, UPDATE , or DELETE statement to a table or view (DML Trigger) CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } AS { sql_statement [ ; ] [ ,...n ] [ ; ] > } ::= [ EXECUTE AS Clause ]

Windows Azure SQL Database Syntax -- Trigger on a CREATE, ALTER, DROP , GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger) CREATE [ OR ALTER ] TRIGGER trigger_name ON { DATABASE } [ WITH [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ,...n ] [ ; ] } ::= [ EXECUTE AS Clause ]

Условно изменяет триггер только в том случае, если он уже существует.

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

имя_триггера
Имя триггера. Объект имя_триггера должно соответствовать правилам для , за исключением того, что имя_триггера не может начинаться с символов # или ##.

table | view
Таблица или представление, в которых выполняется триггер DML, иногда указывается как таблица триггера или представление триггера. Указание уточненного имени таблицы или представления не является обязательным. На представление может ссылаться только триггер INSTEAD OF. Триггеры DML не могут быть описаны в локальной или глобальной временных таблицах.

DATABASE
Применяет область действия триггера DDL к текущей базе данных. Если указано, триггер срабатывает всякий раз, когда event_type или event_group происходит в текущей базе данных.

Применяет область действия триггера DDL или триггера входа к текущему серверу. Если указано, триггер срабатывает всякий раз, когда event_type или event_group возникновении в любом месте на текущем сервере.

Затемняет текст инструкции CREATE TRIGGER. Использование параметра WITH ENCRYPTION не позволяет публиковать триггер как часть репликации SQL Server. Параметр WITH ENCRYPTION не может быть указан для триггеров CLR.

EXECUTE AS
Указывает контекст безопасности, в котором выполняется триггер. Позволяет управлять учетной записью пользователя, используемой экземпляром SQL Server для проверки разрешений на любые объекты базы данных, ссылаемые триггером.

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

NATIVE_COMPILATION
Указывает, что триггер компилируется в собственном коде.

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

SCHEMABINDING
Гарантирует, что таблицы, на которые ссылается триггер не может быть удалена или изменена.

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

FOR | AFTER
Тип AFTER указывает, что триггер DML срабатывает только после успешного выполнения всех операций в инструкции SQL, запускаемой триггером. Все каскадные действия и проверки ограничений, на которые имеется ссылка, должны быть успешно завершены, прежде чем триггер сработает.

Если единственным заданным ключевым словом является FOR, аргумент AFTER используется по умолчанию.

Триггеры AFTER не могут быть определены на представлениях.

INSTEAD OF
Указывает, что триггер DML выполняется вместо запуск инструкции SQL, поэтому, переопределение действия запускающих инструкций. Аргумент INSTEAD OF не может быть указан для триггеров DDL или триггеров входа.

На каждую инструкцию INSERT, UPDATE или DELETE в таблице или представлении может быть определено не более одного триггера INSTEAD OF. Однако можно определить представления на представлениях, где у каждого представления есть собственный триггер INSTEAD OF.

Использование триггеров INSTEAD OF не допускается в поддерживающих обновление представлениях, которые используют параметр WITH CHECK OPTION. SQL Server вызывает ошибку, если триггер INSTEAD OF добавляется к поддерживающему обновление представлению с параметром WITH CHECK OPTION. Пользователь должен удалить этот параметр при помощи инструкции ALTER VIEW перед определением триггера INSTEAD OF.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
Определяет инструкции изменения данных, по которым срабатывает триггер DML, если он применяется к таблице или представлению. Необходимо указать как минимум одну инструкцию. В определении триггера разрешены любые их сочетания в любом порядке.

Для триггеров INSTEAD OF параметр DELETE не разрешен в таблицах, имеющих ссылочную связь с указанием каскадного действия ON DELETE. Аналогично параметр UPDATE не разрешен в таблицах, у которых есть ссылочная связь с указанием каскадного действия ON UPDATE.

Указывает, что требуется добавить триггер существующего типа. Аргумент WITH APPEND не может быть использован для триггеров INSTEAD OF или при явном указании триггера AFTER. Аргумент WITH APPEND может использоваться только при указании параметра FOR без INSTEAD OF или AFTER из соображений поддержки обратной совместимости. Аргумент WITH APPEND не может быть указан, если указан параметр EXTERNAL NAME (в случае триггера CLR).

event_type
Имя языкового события Transact-SQL, которое после выполнения вызывает срабатывание триггера DDL. Допустимые события для триггеров DDL, перечислены в .

event_group
Имя стандартной группы событий языка Transact-SQL. Триггер DDL срабатывает после выполнения какого-либо Transact-SQL событий языка, к которому принадлежит event_group . Список групп событий для триггеров DDL, перечислены в .

После завершения работы, инструкции CREATE TRIGGER event_group также функционирует в качестве макроса, добавляя события соответствующих типов в представление каталога sys.trigger_events.

NOT FOR REPLICATION

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

sql_statement
Условия и действия триггера. Условия триггера указывают дополнительные критерии, определяющие, какие события - DML, DDL или событие входа - вызывают срабатывание триггера.

Действия триггера, указанные в инструкциях языка Transact-SQL, вступают в силу после попытки использования операции.

Триггеры могут содержать любое количество инструкций языка Transact-SQL любого типа, за некоторыми исключениями. Дополнительные сведения см. в подразделе «Примечания». Триггеры разработаны для контроля или изменения данных на основании инструкций модификации или определения данных; они не возвращают пользователю никаких данных. Transact-SQL Часто содержат инструкции в триггере языка управления потоком .

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

SELECT * FROM deleted;

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

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

SQL Serverпозволяет обновить текст , ntext , или изображение триггер столбцов с помощью INSTEAD OF для таблиц или представлений.

Для триггеров в таблицах, оптимизированных для памяти единственным sql_statement разрешено на верхнем уровне-АТОМАРНОГО блока. Допускается внутри блока ATOMIC T-SQL, ограничивается допускается внутри собственного процедуры T-SQL.

< method_specifier >

Указывает метод сборки для связывания с CLR-триггером. Этот метод не должен принимать аргументы и возвращать значения void. class_name должен быть допустимым SQL Server идентификатором и существовать как класс в сборке с видимостью сборки. Если класс имеет имя, содержащее точки (.) для разделения частей пространства имен, имя класса должно быть заключено в квадратные скобки () или двойные кавычки (" "). Класс не может быть вложенным.

Триггеры DML часто используются для применения бизнес-правил и обеспечения целостности данных. В SQL Server декларативное ограничение ссылочной целостности обеспечивается инструкциями ALTER TABLE и CREATE TABLE. Однако декларативное ограничение ссылочной целостности не обеспечивает ссылочную целостность между базами данных. Ограничение ссылочной целостности подразумевает выполнение правил связи между первичными и внешними ключами таблиц. Для обеспечения ограничений ссылочной целостности используйте в инструкциях ALTER TABLE и CREATE TABLE ограничения PRIMARY KEY и FOREIGN KEY. Если ограничения распространяются на таблицу триггера, они проверяются после срабатывания триггера INSTEAD OF и до выполнения триггера AFTER. В случае нарушения ограничения выполняется откат действий триггера INSTEAD OF, и триггер AFTER не срабатывает.

Первые и последние триггеры AFTER, которые будут выполнены в таблице, могут быть определены с использованием процедуры sp_settriggerorder. Для таблицы можно определить только один первый и один последний триггер для каждой из операций INSERT, UPDATE и DELETE. Если в таблице есть другие триггеры AFTER, они будут выполняться случайным образом.

Если инструкция ALTER TRIGGER меняет первый или последний триггер, первый или последний набор атрибутов измененного триггера удаляется, а порядок сортировки должен быть установлен заново с помощью процедуры sp_settriggerorder.

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

Если триггер INSTEAD OF, определенный для таблицы, выполняет по отношению к таблице какую-либо инструкцию, которая бы снова вызвала срабатывание триггера INSTEAD OF, триггер рекурсивно не вызывается. Вместо этого инструкция обрабатывается так, как если бы у таблицы отсутствовал триггер INSTEAD OF, и начинается применение последовательности ограничений и выполнение триггера AFTER. Например, если триггер определен в виде триггера INSTEAD OF INSERT для таблицы и выполняет инструкцию INSERT для этой же таблицы, инструкция INSERT не вызывает нового срабатывания триггера. Команда INSERT, выполняемая триггером, начинает процесс применения ограничений и взвода всех триггеров AFTER INSERT, определенных для данной таблицы.

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

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

Проверка действий инструкций UPDATE или INSERT на указанные столбцы

Триггер языка Transact-SQL можно сконструировать для выполнения конкретных действий, основанных на изменении определенных столбцов с помощью инструкций UPDATE или INSERT. Используйте или в теле триггера для этой цели. Конструкция UPDATE() проверяет действие инструкций UPDATE или INSERT на одном столбце. С помощью конструкции COLUMNS_UPDATED проверяются действия инструкций UPDATE или INSERT, проводимых на нескольких столбцах, и возвращается битовый шаблон, показывающий, какие столбцы были вставлены или обновлены.

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

Инструкция CREATE TRIGGER должна быть первой инструкцией в пакете и может применяться только к одной таблице.

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

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

Одно и то же действие триггера может быть определено более чем для одного действия пользователя (например, INSERT и UPDATE) в одной и той же инструкции CREATE TRIGGER.

Триггеры INSTEAD OF DELETE/UPDATE нельзя определить для таблицы, у которой есть внешний ключ, определенный для каскадного выполнения операции DELETE/UPDATE.

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

Во время срабатывания триггера результаты возвращаются вызывающему приложению так же, как и в случае с хранимыми процедурами. Чтобы предотвратить вызванное срабатыванием триггера возвращение результатов приложению, не следует включать инструкции SELECT, возвращающие результат, или инструкции, которые выполняют в триггере присвоение переменных. Триггер, содержащий либо инструкции SELECT, которые возвращают результаты пользователю, либо инструкции, выполняющие присвоение переменных, требует особого обращения; эти возвращаемые результаты должны быть перезаписаны во все приложения, в которых разрешены изменения таблицы триггера. Если в триггере происходит присвоение переменной, следует использовать инструкцию SET NOCOUNT в начале триггера, чтобы предотвратить возвращение каких-либо результирующих наборов.

Хотя инструкция TRUNCATE TABLE по своей сути является инструкцией DELETE, она не активирует триггер, поскольку операция не записывает удаление отдельных строк. Однако беспокоиться о случайном обходе триггера DELETE таким образом нужно только пользователям с разрешениями на выполнение инструкции TRUNCATE TABLE.

Инструкция WRITETEXT (с ведением журнала и без него) не запускает триггеры.

Следующие инструкции языка Transact-SQL не разрешены в триггерах DML:

Триггеры DDL, как и стандартные триггеры, выполняют хранимые процедуры в ответ на какое-либо событие. В отличие от стандартных триггеров, они не срабатывают в ответ на выполнение инструкций UPDATE, INSERT или DELETE по отношению к таблице или представлению. Вместо этого триггеры срабатывают в первую очередь в ответ на инструкции языка определения данных (DDL). Это инструкции CREATE, ALTER, DROP, GRANT, DENY, REVOKE и UPDATE STATISTICS. Системные хранимые процедуры, выполняющие операции, подобные операциям DDL, также могут запускать триггеры DDL.

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

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

В отличие от триггеров DML, триггеры DDL не ограничены областью схемы. Поэтому для запроса метаданных о триггерах DDL нельзя воспользоваться такими функциями как OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY и OBJECTPROPERTYEX. Используйте вместо них представления каталога. Дополнительные сведения см. в разделе .

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

Если проверка подлинности завершается сбоем, триггеры входа не срабатывают.

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

Отключение триггера входа

Триггер входа может эффективно запрещать подключения к службам Компонент Database Engine для всех пользователей, в том числе членов предопределенной роли сервера sysadmin . Если триггер входа запрещает соединения, члены предопределенной роли сервера sysadmin могут подключаться с помощью выделенного административного соединения или путем вызова Компонент Database Engine в режиме минимальной конфигурации (-f). Дополнительные сведения см. в разделе .

Возвращаемые результаты

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

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

Несколько триггеров

SQL Server позволяет создавать несколько триггеров для каждого события DML, DDL и LOGON. Например, если инструкция CREATE TRIGGER FOR UPDATE выполняется в таблице, уже имеющей триггер UPDATE, дополнительно создается триггер обновления. В более ранних версиях SQL Server был разрешен только один триггер в каждой таблице для каждого события изменения данных INSERT, UPDATE или DELETE.

Рекурсивные триггеры

SQL Server разрешает рекурсивный вызов триггеров, если с помощью инструкции ALTER DATABASE включена настройка RECURSIVE_TRIGGERS.

В рекурсивных триггерах могут возникать следующие типы рекурсии:

Косвенная рекурсия

При косвенной рекурсии приложение обновляет таблицу T1. Это событие вызывает срабатывание триггера TR1, обновляющего таблицу T2. Это вызывает срабатывание триггера T2 и обновление таблицы T1.

Прямая рекурсия

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

В следующем примере используются оба типа рекурсий: прямая и косвенная. Допустим, для таблицы T1 определены два триггера: TR1 и TR2. Триггер TR1 рекурсивно обновляет таблицу T1. Инструкция UPDATE выполняет каждый из триггеров TR1 и TR2 один раз. В дополнение к этому срабатывание триггера TR1 вызывает выполнение триггеров TR1 (рекурсивно) и TR2. В таблицах inserted и deleted триггера содержатся строки, которые относятся только к инструкции UPDATE, вызвавшей срабатывание триггера.

Отключение настройки RECURSIVE_TRIGGERS предотвращает выполнение только прямых рекурсий. Чтобы отключить косвенную рекурсию, с помощью хранимой процедуры sp_configure присвойте параметру сервера nested triggers значение 0.

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

Вложенные триггеры

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

Чтобы отменить вложенные триггеры, присвойте значение 0 параметру nested triggers хранимой процедуры sp_configure. В конфигурации по умолчанию вложенные триггеры разрешены. Если вложенные триггеры отключены, рекурсивные триггеры тоже будут отключены, вне зависимости от настройки RECURSIVE_TRIGGERS, установленной с помощью инструкции ALTER DATABASE.

Первый триггер AFTER, вложенный в INSTEAD OF, триггер срабатывает, даже если вложенные триггеры параметр конфигурации сервера задано значение 0. Однако при таком значении параметра последующие триггеры AFTER не срабатывают. Рекомендуется проверить приложения на наличие вложенных триггеров определить, соответствуют ли приложения бизнес-правилам это поведение при вложенные триггеры параметр конфигурации сервера имеет значение 0 и внесите соответствующие изменения.

Отложенная интерпретация имен

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

Для создания триггера DML требуется разрешение ALTER на таблицу или представление, в которых создается триггер.

Для создания триггера DDL с областью действия в пределах сервера (ON ALL SERVER) или триггера входа требуется разрешение CONTROL SERVER на сервер. Для создания триггера DDL с областью видимости в пределах базы данных (ON DATABASE) требуется разрешение ALTER ANY DATABASE DDL TRIGGER на текущую базу данных.

A. Использование триггера DML с предупреждающим сообщением

Следующий триггер DML отправляет клиенту сообщение, когда кто-то пытается добавить или изменить данные в таблице Customer в базе данных AdventureWorks2012.

CREATE TRIGGER reminder1 ON Sales.Customer AFTER INSERT , UPDATE AS RAISERROR ("Notify Customer Relations" , 16 , 10 ); GO

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

В следующем примере указанному пользователю (MaryM) по электронной почте отправляется сообщение при изменении таблицы Customer .

CREATE TRIGGER reminder2 ON Sales.Customer AFTER INSERT , UPDATE , DELETE AS EXEC msdb.dbo.sp_send_dbmail @profile_name = "AdventureWorks2012 Administrator" , @recipients = "[email protected]" , @body = "Don""t forget to print a report for the sales force." , @subject = "Reminder" ; GO

В. Использование триггера DML AFTER для принудительного применения бизнес-правил между таблицами PurchaseOrderHeader и Vendor

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

В следующем примере создается триггер DML в базе данных AdventureWorks&2012;. Этот триггер выполняет проверку кредитоспособности поставщика является хорошим (не 5) при попытке вставить новый заказ на покупку в PurchaseOrderHeader таблицы. Для получения сведений о кредитоспособности поставщика требуется ссылка на таблицу Vendor . В случае слишком низкой кредитоспособности выводится соответствующее сообщение и вставка не выполняется.

This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table -- when the credit rating of the specified vendor is set to 5 (below average). CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader AFTER INSERT AS IF EXISTS (SELECT * FROM Purchasing.PurchaseOrderHeader AS p JOIN inserted AS i ON p .PurchaseOrderID = i .PurchaseOrderID JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = p .VendorID WHERE v.CreditRating = 5 ) BEGIN RAISERROR ("A vendor""s credit rating is too low to accept new purchase orders." , 16 , 1 ); ROLLBACK TRANSACTION ; RETURN END ; GO -- This statement attempts to insert a row into the PurchaseOrderHeader table -- for a vendor that has a below average credit rating. -- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back. INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status , EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight) VALUES (2 ,3 ,261 ,1652 ,4 ,GETDATE () ,GETDATE () ,44594.55 ,3567.564 ,1114.8638 ); GO

Г. Использование триггера DDL уровня базы данных

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

CREATE TRIGGER safety ON DATABASE FOR DROP_SYNONYM AS RAISERROR ("You must disable Trigger "safety" to drop synonyms!" ,10 , 1 ) ROLLBACK GO DROP TRIGGER safety ON DATABASE ; GO

Д. Использование триггера DDL уровня сервера

В следующем примере триггер DDL используется для вывода сообщения при возникновении на данном экземпляре сервера любого из событий CREATE DATABASE, а функция EVENTDATA используется для получения текста соответствующей инструкции на языке Transact-SQL. Дополнительные примеры использования функции EVENTDATA в триггерах DDL см. .

CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT "Database Created." SELECT EVENTDATA ().value ("(/EVENT_INSTANCE/TSQLCommand/CommandText)" ,"nvarchar(max)" ) GO DROP TRIGGER ddl_trig_database ON ALL SERVER ; GO

Е. Использование триггера входа

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

USE master ; GO CREATE LOGIN login_test WITH PASSWORD = "3KHJ6dhx(0xVYsdf" MUST_CHANGE, CHECK_EXPIRATION = ON ; GO GRANT VIEW SERVER STATE TO login_test; GO CREATE TRIGGER connection_limit_trigger ON ALL SERVER WITH EXECUTE AS "login_test" FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= "login_test" AND (SELECT COUNT (*) FROM sys .dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = "login_test" ) >