Синтаксис триггеров, системные команд! и функции

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

Конструкции UPDATE толбца) и (COLUMNS UPDATED())

Дне конструкции - UPDATE томца)и S l.PDATFDfU - являются важными компонентами операторов CREATE TRIGGER и ALTER TRIGGER. Эти конструк-разрешается включать в UPDATE- и и они могут в

любом месте оператора CREATE TRIGGER или ALTER TRIGGER.

Конструкция IF UPDATE {имястолбца) определяет, произошло ли в столбце мл1Я сто/гбцасобытие INSERT или UPDATE. Если нужно задать несколько столбцов, следует разделить их конструкциями UPDATE <имя столбца). Например, следующий фрагмент кода проверяет, выполнено ли добавление или обновление в столбцах First Name и

Last Nattie, и выполняет некоторые действия над этим [хами в результате событий

INSERT или UPDATE:

IF UPDATE name) OR UPDATE (Last Name) BEGIN

- Если какой-нибудь из столбцов обновляется. выполнить над ним какие-либо действия.

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

Если подставить значение вместо толбца, конструкции DATE вернет TRUE.

также проверяет, произошло ли обновление столбцов. Вместо true или false конструкция (COLUMNS U PDATED( Ц возвращает битовую маску тип отисываюшую столбцы, в которгх выполнено добавление или обновление.

Написать конструкцию (COLUMNS UPDATED()) сложнее, чем UPDATE {имя стол-

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

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

Столбец I 2 3 4 5 6 7 8

Битовая маска 1 2 4 8 16 32 64 128

Следующий код позволят проверить, были ли добавлены или обновлены данные в столбцах 4 или 6:

updatedo & 40) > о

Значение 40 - это результат суммирования маски 8 для столбца 4 и маски 32 для столбца 6. Выражение проверяет, действительно ли значение (COl.UMNS UPnATEDm больше 0. Другими словами, условие выполняется, если хотя бы один из двух или оба столбца обновлены. Если задать условие (COLUMNS UPDATED() & 40) = 40, то проверка oOriOB-

290 Триггеры ~ Гпааа 9

выполняется в обоих Если обновление произошло только в одном из стол-

бцов, условие не выполнится.

Чтобы проверить девять и больше ..молбпов. следует использовать функцию SUBSTRING, которая позволяет указать триггеру маску, проверке. Например, сле-дующтт код позволяет проверить, обновлен ли девятый столбец:

IF ((SUBSTRING(COLUMNS UPDATED(), 2, 1)-1))

Функция SUBSTRING COLUMNS UPDATED()) перейти ко второму октету и проверить, обновлен ли первый столбец второго октета (его ре-

альный порядковый номер равен 9). Для этого столбца возврашается значение типа varbinary, равное 1. В показанной далее таблиц i иллюстрируется принцип действия функции SUBSTRING, необходимой для проверки столбцов с 9 по 16. .. .

IF ((SUBSTRING(COLUMNS UPDATED{),:.yj , ,у

Столбец 9 10 11 12 13 14 15 16

Значения у и z 1 2 4 8 16 32 64 128

Чтобы проверить несколько таЕбиов на предмет модификации, следует просто сложить значения битовой маски для каждого из них. Например, чтобы проверить столбцы 14 и 16, нужно задать для ние 160 (32 + 128).

Функции и системные команды

Для бизнес-логики в триггерах предназначены различные функции и систем-

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

число строк, на которое повлияло ис предыдущего оператора Триг-

гер срабатывает на событие INSERT, UPDATE или DELETE, даже если при этом не из-

ни одна строка. Поэтому для выхода из триггера при отсутствии таблицы используется системная Kov(aH;i:, RETURN.

В случае возникновения ошибки иногда требуется вывести сообщение с описанием ее причины. Для вывода сообщений об ошибках используется системная команда RAIS-ERROR. Пользовательские сообщения об ошибках создают с помошью системной хранимой процеду:: spaddmessage или выводят встроенные сообщения при вызове системной команды RAISERROR. За дополнительной информацией о системной хранимой процедуре sp addmessage обращайтесь в SQL Server Books Online.

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

возникает фатальная ошибка), то в код триггера можно не включать системную команду

ROLLBACK TRANSACTION.

Меры предосторожности пр овании языка Transact-SQL

В коде триггеров применяют операторы SELECT и PRINT, а также значения

переменным. Однако этих операторов для возврата набо-

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

оператора SELECT как поставщика для оператора проверки условия. На-

пример, можно применить оператор SELECT для проверки существования некоторого значения и возврата этого значения оператору IF EXISTS для дальнейшей обработки. Чтобы присваивать значения переменным, необходимо включить в текст программы триггера команду SET NOCOUNT ON.

В триггерах недопустимы следующие операторы

ALTER, CREATE, DROP, RESTORE и LOAD DATABASE;

LOAD и RESTORE LOG;

DISK RESIZE и DISK 1 NIT;

RECONFIGURE.

Распространенные задачи, решаемые посредством триггеров

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

Расчет промежуточных результатов и других в1числяемгх значений. База данных постоянно меняется по мере добавления, удаления и модификации данных в ее таблицах. В некоторые чаях данные столбца одной таблицы можно рассчитывать на основе модифицируемых данных дру1 ой таблицы. Триггеры представляют собой идеальное средство реализации вычисляемых столбцов. На рис. 9-3 показан триггер, который используется для обновления розничной цены товара в таблице Inventory при изменении средней оптовой цены в таблице Purchasing.

Рис. 9-3. Таблицы Inventory и Purchasing

триггер привязан к таблице Purchasing. Он рассчитывает среднюю цену для товара на складе чиваеч ее на 30% и обновляет значение столбца retail price в таблице Inventory:

CREATE TRIGGER Retail Price Update

ONp..irC( vlaLiiy

AFTER INSERT, UPDATE, DELETE AS SET NOCOUNT ON

IF EXISTS (SELECT iteni num FROM inserted) BEGIN UPDATE inventory SE ice =

(SELECT (SUM(pur.total cost),/SUM(pijr. aty orcered) - 1.0) FROM purchasing pur INNER JOIN inserted ins em num = ins. iteiri nufTi)

11-2061