Триггеры

Обычные хранимые процедуры запускаются из кода клиентской программы или из другой хранимой процедуры. Триггеры (triggers) < $I[]триггер (trigger)> — это процедуры, решение о выполнении которых «принимает» SQL Server, а не клиентская программа. Кроме того, триггер определяется для конкретной таблицы и выполняется при попытке изменения данных в этой таблице. В SQL Server используются триггеры DELETE, INSERT, UPDATE, каждый из которых срабатывает при выполнении одноименной команды. Можно создавать триггеры, «реагирующие» на более чем одну команду.

SQL Server поддерживает два типа триггеров: AFTER и INSTEAD OF. Триггеры AFTER «срабатывают» после выполнения команды (DELETE, INSERT, UPDATE), для которой они предназначены, а триггеры INSTEAD OF — вместо нее. Триггеры AFTER можно создавать только для таблиц, но не для представлений, а триггеры INSTEAD OF — и для таблиц, и для представлений. Для каждой из команд (DELETE, INSERT, UPDATE) может быть установлено несколько триггеров AFTER. Для каждой из этих команд () может быть установлен только один триггер INSTEAD OF.

Для создания триггера нужно использовать инструкцию CREATE TRIGGER < $I[] CREATE TRIGGER > со следующим синтаксисом:

Синтаксис

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [DELETE] [, ] [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 ]
}
}

Элементы синтаксиса:

trigger_name — имя триггера (должно отвечать обычным правилам именования объектов SQL Server и быть уникальным в базе данных).

ON { table | view } — предложение, которое определяет таблицу (или представление), для которой создается триггер.

WITH ENCRYPTION — предложение, указывающее на то, что текст триггера будет зашифрован.

AFTER — ключевое слово, указывающее на то, что триггер будет «запущен» только, когда все операции, с которыми связан триггер, выполнятся успешно, в том числе и все ссылочные каскадные действия (referential cascade actions) и ограничительные проверки (constraint checks). Ключевое слово FOR означает то же самое, что и AFTER, и используется только для совместимости с SQL Server 7.

INSTEAD OF — предложение, определяющее, что триггер выполняется вместо SQL-оператора, для которого он объявлен. По большей части, для таблицы или представления может определяться один триггер типа INSTEAD OF на оператор INSERT, UPDATE или DELETE. Однако возможно определять представления для представлений, где каждое представление имеет собственный триггер тира INSTEAD OF.

{ [ DELETE ] [, ] [ INSERT ] [, ] [ UPDATE ] } — ключевые слова, которые определяют, какие операторы модификации данных активизируют триггер (при попытке использования в отношении данной таблицы или представления). По крайней мере, одно из этих ключевых слов должно определяться. В определении триггера разрешается любая комбинация этих ключевых слов в любой последовательности. Если определяется более одной опции, следует разделять слова запятыми. Для триггеров типа INSTEAD OF опция DELETE не разрешается для таблиц, которые имеют ссылочное отношение, определяющее каскадное действие ON DELETE. Аналогично, опция UPDATE не разрешается для таблиц, которые имеют ссылочное отношение, определяющее каскадное действие ON UPDATE.

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

AS — ключевое слово, обозначающее начало кода триггера (как и в синтаксисе хранимой процедуры).

sql_statement — «триггерное условие» (условиями) или действие (действиями). «Триггерные условия» определяют дополнительные критерии, которые приводят попытки выполнения оператора DELETE, INSERT или UPDATE к выполнению «триггерного действия» (действий). «Триггерные действия», определяемые в операторах T-SQL, вступают в силу, когда предпринимается попытка выполнения операции DELETE, INSERT или UPDATE. Триггеры могут включать любое количество и любой тип операторов T-SQL. Триггер предназначен для проверки или изменения данных, исходя из оператора модификации данных; он не должен возвращать данные пользователю. Операторы T-SQL в триггере часто включают язык управляющих операторов (control-of-flow language).

n — заполнитель (placeholder), обозначающий, что в триггер могут быть включены много операторов Transact-SQL. Для оператора IF UPDATE (column) много столбцов могут быть включены путем повторения предложения UPDATE (column).

IF UPDATE (column)— предложение, которое тестирует INSERT- или UPDATE-действия для определенного столбца и не используется с операциями DELETE. Определять можно более одного столбца. Поскольку имя таблицы определяется в предложении ON, не следует включать имя таблицы перед именем столбца в предложении IF UPDATE. Для тестирования INSERT- или UPDATE-действия для более чем одного столбца определяйте отдельное предложение UPDATE (column), следующеепослепервогопредложения. Предложение IF UPDATE возвращает значение TRUE в INSERT-действиях, потому что столбцы имеют вставленные явные, либо неявные (NULL) значения.

UPDATE (column) — предложение, которое может использоваться в любом месте в теле триггера.

column — имя столбца для тестирования INSERT- или UPDATE-действия. Этот столбец может быть любого типа данных, поддерживаемого SQL Server. Однако в этом контексте не могут использоваться вычисляемые столбцы.

IF (COLUMNS_UPDATED ()) — предложение, которое тестирует (только в триггере INSERT или UPDATE), был ли упомянутый столбец или столбцы добавлен или обновлен. COLUMNS_UPDATED возвращает битовую комбинацию (тип varbinary), которая обозначает, какие столбцы в таблице были вставлены или обновлены. Функция COLUMNS_UPDATED возвращает биты в последовательности слева направо с наименьшим значащим битом в крайней левой позиции. Самый левый разряд представляет первый столбец в таблице; следующий бит справа представляет второй столбец и так далее. Если таблица, для которой создается триггер, содержит более 8 столбцов, COLUMNS_UPDATED возвращает несколько байт с наименьшим значащим байтом в крайней левой позиции. COLUMNS_UPDATED возвращает значение TRUE для всех столбцов в действиях INSERT, потому что столбцы имеют вставленные либо явные, либо неявные (NULL) значения. COLUMNS_UPDATED можно использовать в любом месте в теле триггера.

bitwise_operator — побитовый оператор для использования в сравнении.

updated_bitmask — битовая маска столбцов, фактически обновленных или вставленных.

comparison_operator — оператор сравнения. Используйте знак равенства (=) для проверки того, являются ли все столбцы, определенные в updated_bitmask, действительно обновленными. Используйте символ «больше чем» (>) для проверки того, обновлены ли какие-либо (или некоторые) столбцы, определенные в updated_bitmask.

column_bitmask — битовая маска столбцов для проверки того, обновлены они или вставлены.

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

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

В помощь разработчику предоставляется функция UPDATE для проверки того, изменилось ли значение определенного поля в записи. Единственным аргументом этой функции является имя поля таблицы. Возвращаемыми значениями функции являются TRUE (значение поля изменилось) и FALSE (значение поля не изменилось).

Основное назначение триггеров — это реализация дополнительных ограничений на данные в таблицах базы данных в соответствии с бизнес-правилами эксплуатирующей эту базу компании. Чаще всего приводится пример обработки инвентаризационной ведомости при поступлении в базу данных, хранимую на сервере, очередной расходной или приходной накладной. Для решения подобной задачи можно обойтись и обычным вызовом хранимой процедуры, но применение триггера, который, практически, «без участия» пользователя «проснется» и изменит инвентаризационную ведомость, конечно, изящней.

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

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

Листинг 21.12 Триггер, не позволяющий «принимать» в ремонт одно и то же изделие в течение одного дня

1: CREATE TRIGGER InsRemTr ON Ремонты

2: FOR INSERT, UPDATE

3: AS

4: DECLARE @НомерИзделия as varchar(15), @ДатаВремонт datetime, @count as int

5: /* найти номер изделия и дату поступления в ремонт */

6: SELECT @НомерИзделия = i.НомерИзделия, @ДатаВремонт = i.ДатаВремонт

7: FROM Ремонты s, Inserted i

8: WHERE s.НомерИзделия = i.НомерИзделия

9: AND s.ДатаВремонт = i.ДатаВремонт

10: /* определить количество строк с одинаковыми НомерИзделия и ДатаВремонт */

11: SELECT @count =count(НомерИзделия)

12: FROM Ремонты

13: WHERE НомерИзделия = @НомерИзделия

14: AND ДатаВремонт = @ДатаВремонт

15: IF @count > 1

16: BEGIN

17: ROLLBACK TRAN

18: RAISERROR('Не может быть двух приемов в ремонт за один день! ', 10, 16)

19: END

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

Одно из решений очень простое: создать еще один справочник и при помощи триггеров обеспечивать наличие строк этого справочника со строками в основном справочнике. Например, при записи в основной справочник (любой из неизвестных вам программ) нового товара можно при помощи триггера добавить такой же код и в новый справочник.

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

CREATE TABLE Номенклатура2 (

[КодТовара] [varchar] (12) NOT NULL,

[Наименование2Товара] [varchar] (60) DEFAULT '',

[Цена2] [money] DEFAULT 0

)

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

/* вставка в таблицу Номенклатура2 при добавлении в таблицу Номенклатура, */

DROP TRIGGER ВставкаВНоменклатура2

GO

CREATE TRIGGER ВставкаВНоменклатура2 ON Номенклатура

FOR INSERT

AS

DECLARE @icount1 int

DECLARE @КодТовара as varchar(12)

SELECT @КодТовара = КодТовара FROM Inserted

SELECT @icount1=Count(*) FROM Номенклатура2 WHERE @КодТовара = КодТовара

IF @icount1=0

BEGIN

INSERT INTO Номенклатура2 (КодТовара, Наименование2Товара, Цена2)

VALUES (@КодТовара, '', 0.0)

END

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

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

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

[2] SQL Server 7.0 не имеет возможности для создания функций пользователя.