Главная » Microsoft SQL Server, Базы данных » Основы триггеров

0

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

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

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

В SQL Server 2005 существуют два типа триггеров транзакций: INSTED OF (триггер замены операции) и AFTER (триггер, выполняющийся сразу после операции). Эти типы отличаются своим назначением, временем выполнения и производимым эффектом. Все это детализировано в табл. 23.1.

Триггеры базы данных могут запускаться и командами языка определения дан- Новинка ^ нь,х DD*” — CREATE> alter и drop. Поскольку эти триггеры оперируют на уров- 2005       не схемы базы данных, они рассмотрены в главе 17.

Таблица 23.1. Сравнение типов триггеров

Триггер INSTEAD OF

Триггер AFTER

Инструкция DML

Автоматически откатывается

Выполняется, если триггер сам не

откатит транзакцию

Время выполнения

Перед ограничениями первичного и

После выполнения транзакции, но

внешнего ключей

перед ее подтверждением

Количество возможных событий

Одно

Несколько

таблицы

Можно ли применять к пред

Да

Нет

ставлениям?

Допустима ли вложенность?

В зависимости от параметров сер

В зависимости от параметров

вера

сервера

Допустима ли рекурсивность?

Нет

В зависимости от параметров

сервера

Порядок выполнения транзакций

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

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

1.              Проверка Identity Insert.

2.              Ограничение допустимости пустых значений.

3.              Проверка типа данных.

4.              Выполнение триггера INSTEAD OF. Если такой триггер существует, то выполнение инструкции DML останавливается в этой точке. Триггеры INSTEAD OF не могут быть рекурсивными. Таким образом, если триггер, запущенный событием некоторой инструкции, снова выполняет ту же инструкцию (INSERT, UPDATE или DELETE), во второй раз его присутствие игнорируется.

5.              Ограничение первичного ключа.

6.              Ограничение проверки.

7.              Ограничение внешнего ключа.

8.              Выполнение инструкции DML и обновление журнала транзакций.

9.              Выполнение триггера AFTER.

10.          Подтверждение транзакции.

11.          Запись в файл данных.

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

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

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

?               Триггер AFTER может принимать во внимание, что данные уже прошли все встроенные проверки целостности данных.

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

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

Дополнительная Триггеры могут быть написаны с использованием CLR и любого языка семейст- информация ва NEy Более подробно этот вопрос будет рассмотрен в главе 27.

Создание триггеров

Триггеры создаются и модифицируются с помощью стандартных команд языка DDL CREATE, ALTER и DROP следующим образом:

CREATE TRIGGER имя_триггера ON имя_таблицы AFTER Insert, Update Delete AS

программный_код_триггера

До выхода версии SQL Server 2000 в SQL Server существовали только триггеры AFTER. Поскольку не было острой необходимости разделять триггеры INSTEAD OF и AFTER, был сохранен старый синтаксис FOR INSERT, UPDATE или DELETE. Для обеспечения работоспособности старых триггеров AFTER они могут создаваться с использованием ключевого слова FOR вместо AFTER.

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

Несмотря на то что я настойчиво рекомендую создавать и изменять триггеры с помощью сценариев и управления версиями, вы можете просматривать и редактировать триггеры в окне Object Explorer утилиты Management Studio (рис. 23.1).

Рис. 23.1. В окне Object Explorer перечислены все триггеры каждой таблицы. С помощью контекстного меню пользователь может редактировать эти триггеры

Триггеры AFTER

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

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

И         сложная проверка данных;

О         поддержка сложных правил бизнес-логики;

Я          запись журнала аудита данных;

?               обслуживание измененных столбцов данных;

?               реализация пользовательских проверок целостности данных и каскадных удалений.

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

Когда изучается какой-либо язык программирорвания, обычно первой написанной программой является ввод на экран традиционной фразы “Hello, world”. При этом от вас больше ничего не требуется, кроме как скомпилировать программу и убедиться, что эта фраза действительно отображается на экране. Проводя аналогию, следующий триггер AFTER при выпол-

USE Family;

CREATE TRIGGER TriggerOne ON Person AFTER Insert AS

PRINT ‘After Trigger';

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

INSERT Person(PersonID, LastName, FirstName, Gender)

VALUES (50, ‘Ebob’, ‘Bill’,’M’);

Получим следующий результат:

After Trigger (1 row(s) affected)

Как мы видим, инструкция вставки была выполнена, и тригер вывел на экран заложенное в него сообщение.

Триггеры INSTEAD OF

Триггер INSTEAD OF заменяет транзакцию (т.е. выполняется вместо нее). Это подобно тому, как если бы триггер автоматически откатил транзакцию, для которой был создан.

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

Не путайте триггеры INSTEAD OF с триггерами BEFORE или событиями BEFORE UPDATE. Это не одно и то же. Триггер BEFORE, если бы таковой существовал в SQL Server, не пересекался бы с транзакцией, если бы в самом триггере не была предусмотрена инструкция отката.

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

?               Инструкция DML пытается обновить необновляемое представление. В этом случае триггер INSTEAD OF обновляет таблицы, на которых построено данное представление.

?               Инструкция DML пытается напрямую обновить таблицу складских остатков, что недопустимо. В этом случае триггер INSTEAD OF обновляет таблицу складских операций.

?               Инструкция DML пытается удалить строку, а триггер INSTEAD OF вместо этого перемещает данную строку в архивную таблицу.

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

CREATE TRIGGER TriggerTwo ON Person INSTEAD OF Insert AS

PRINT ‘Instead of Trigger’ go

INSERT Person(PersonID, LastName, FirstName, Gender)

VALUES (51, ‘Ebob’, ”,’M’)

Будет получен следующий результат:

Instead of Trigger (1 row(s) affected)

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

SELECT LastName FROM Person WHERE PersonID =51

LastName

(0 row(s) affected)

В данном случае инструкция INSERT отработала так, как будто одна строка была обработана, хотя эффект этой инструкции был блокирован триггером INSTEAD OF. Вместо вставки строки была выполнена операция вывода на экран заложенной в триггер фразы. Заметим, что созданный ранее триггер AFTER остался в силе, однако его сообщение не было выведено на экран.

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

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

?               CREATE, ALTER и DROP (создание, изменение и удаление таблицы);

?               RECONFIGURE (изменение конфигурации);

?               RESTORE (восстановление базы данных или журнала);

?               DISK RESIZE (изменение размеров дискового пространства);

?               DISK INIТ (инициализация диска).

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

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

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

ALTER TABLE имя_та блицы DISABLE TRIGGER имя_триггера ALTER TABLE имя_ та блицы ENABLE TRIGGER имя_триггера

В следующем примере отключается триггер INSTEAD OF с названием TriggerOne таблицы Person:

ALTER TABLE Person

DISABLE TRIGGER TriggerOne

Для просмотра состояния триггера (включен он или отключен) используется функция objectproperty (), которой передается идентификатор триггера и параметр Ехес IsTriggerDisabled:

SELECT OBJECTPROPERTY(

OBJECT_ID(‘TriggerOne’)# 1ExecIsTriggerDisabled’)

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

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

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

SELECT substring(S2.Name,1,30) as [Table], substring(S.Name, 1,30) as [Trigger],

CASE (SELECT — Correlated subquery

OBJECTPROPERTY(OBJECT_ID(S.Name),

‘ExecIsTriggerDisabled1))

WHEN 0 THEN ‘Включен’

WHEN 1 THEN ‘Отключен’

END AS Status FROM Sysobjects S JOIN Sysobjects S2

ON S.parent_obj = S2.ID WHERE S.Type = ‘TR’

ORDER BY [Table], [Trigger]

Будет получен следующий результат:

Table           Trigger                                       Status

Person         Person_Parents                          Включен

Person         TriggerOne                                Отключен

Person        TriggerTwo                                Включен

Триггеры и безопасность

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

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии