Триггеры в ms sql server примеры. Триггеры в MS SQL Server Обзор триггеров

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

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

Синтаксические обозначения в Transact-SQL

Синтаксис

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE 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 } ::= assembly_name.class_name.method_name Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger) CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= assembly_name.class_name.method_name Trigger on a LOGON event (Logon Trigger) CREATE TRIGGER trigger_name ON ALL SERVER [ WITH [ ,...n ] ] { FOR| AFTER } LOGON AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= assembly_name.class_name.method_name

Аргументы

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

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

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

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

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

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

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

EXECUTE AS, предложение (Transact-SQL) .

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.

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

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

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

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

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

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

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

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

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

SELECT * FROM deleted

Дополнительные сведения см. в разделе Использование таблиц inserted и deleted .

Триггеры DDL и триггеры входа собирают сведения о событиях, запускаемых с помощью функции EVENTDATA (Transact-SQL) . Дополнительные сведения см. в разделе Использование функции EVENTDATA .

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

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

Замечания

Триггеры DML

Триггеры 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 была успешно выполнена. Успешное выполнение также подразумевает завершение всех ссылочных каскадных действий и проверки ограничений, связанных с измененными или удаленными объектами.

Если триггер 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() или COLUMNS_UPDATED . Конструкция 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:

RESTORE DATABASE

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

Триггеры DDL

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

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

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

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

Триггеры входа

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

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

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

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

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

Общие соглашения о триггерах

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

Возможность возвращать результаты из триггеров будет исключена из следующей версии SQL Server. Триггеры, возвращающие результирующие наборы, могут привести к непредвиденному поведению приложений, не предназначенных для работы с ними. Избегайте в текущих разработках использования триггеров, возвращающих результирующие наборы, и запланируйте изменения в приложениях, которые сейчас используют их. Чтобы триггеры не возвращали результирующие наборы, для параметра disallow results from triggers необходимо установить значение 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 предотвращает выполнение только прямых рекурсий. Чтобы отключить и косвенную рекурсию, нужно установить параметр сервера nested triggers в состояние 0 с помощью процедуры sp_configure.

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

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

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

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

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

Отложенное разрешение имен

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

Разрешения

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

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

Примеры

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

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

USE AdventureWorks2008R2; GO IF OBJECT_ID ("Sales.reminder1", "TR") IS NOT NULL DROP TRIGGER Sales.reminder1; GO CREATE TRIGGER reminder1 ON Sales.Customer AFTER INSERT, UPDATE AS RAISERROR ("Notify Customer Relations", 16, 10); GO

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

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

USE AdventureWorks2008R2; GO IF OBJECT_ID ("Sales.reminder2","TR") IS NOT NULL DROP TRIGGER Sales.reminder2; GO CREATE TRIGGER reminder2 ON Sales.Customer AFTER INSERT, UPDATE, DELETE AS EXEC msdb.dbo.sp_send_dbmail @profile_name = "AdventureWorks2008R2 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. Этот триггер проверяет уровень кредитоспособности поставщика при попытке добавить новый заказ на покупку в таблицу PurchaseOrderHeader. Для получения сведений о кредитоспособности поставщика требуется ссылка на таблицу Vendor. В случае слишком низкой кредитоспособности выводится соответствующее сообщение, и вставка не производится.

USE AdventureWorks2008R2; GO IF OBJECT_ID ("Purchasing.LowCredit","TR") IS NOT NULL DROP TRIGGER Purchasing.LowCredit; GO -- 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 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 используется для предотвращения удаления синонимов в базе данных.

USE AdventureWorks2008R2; GO IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = "safety") DROP TRIGGER safety ON DATABASE; GO 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.

IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = "ddl_trig_database") DROP TRIGGER ddl_trig_database ON ALL SERVER; GO 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") > 3 ROLLBACK; END;

Ж. Просмотр событий, вызвавших срабатывание триггера

В следующем примере выполняются запросы к представлениям каталога sys.triggers и sys.trigger_events с целью определения, какие события языка Transact-SQL вызывали срабатывание триггера safety. Создание триггера safety показано в предыдущем примере.

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

Назначение триггеров

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

Объявление триггеров

CREATE TRIGGER {BEFORE|AFTER } {DELETE|INSERT|UPDATE [OF ]} ON REFERENCING {OLD {[ROW ]|TABLE [AS ] } NEW {ROW|TABLE } [AS ] }] [FOR EACH {STATEMENT|ROW [WHEN ]}]
[BEGIN ATOMIC ]

[END ]

Ключевые слова

. BEFORE|AFTER – время запуска триггера – до | после операции обновления.
. DELETE|INSERT|UPDATE = событие срабатывания триггера.
. FOR EACH ROW – для каждой строки (строчный триггер, тогда и WHEN).
. FOR EACH STATEMENT – для всей команды (действует по умолчанию).
. REFERENCING – позволяет присваивать до 4-х псевдонимов старым и | или новым строкам и | или таблицам, к которым могут обращаться триггера.

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

Тело триггера не может содержать операторов:
. Определения, удаления и изменения объектов БД (таблиц, доменов и т.п.)
. Обработки транзакций (COMMIT, ROLLBACK)
. Подключения и отключения к БД (CONNECT, DISCONNECT)

Особенности применения
. Триггер выполняется после применения всех других (декларативны) проверок целостности и целесообразен тогда, когда критерий проверки достаточно сложен. Если декларативные проверки отклоняют операцию обновления, то до выполнения триггеров дело не доходит. Триггер работает в контексте транзакции, а ограничение FK нет.
. Если триггер вызывает дополнительную модификацию своей базовой таблицы, то чаще всего это не приводит к его рекурсивному выполнению, однако это следует уточнять. В СУБД SQL Server 2005 предусмотрена возможность указания рекурсии до 255 уровней с помощью ключевого слова OPTION (MAXRECURSIV 3).
. Триггеры обычно не выполняются при обработке больших двоичных столбцов (BLOB).
. Следует помнить, что всякий раз при обновлении данных СУБД автоматически создает так называемые триггерные виртуальные таблицы, которые в различных СУБД носят разные название. В InterBase и Oracle – Это New и Old. В SQL Server – Inserted и Deleted. Причем при изменении данных создаются обе. Эти таблицы имеют то же количество столбцов, с теми же именами и доменами, что и обновляемая таблица. В СУБД SQL Server 2005 предусмотрена возможность указания таблицы, включая временную, в которую следует вставить данные с помощью ключевого слова OUTPUT Inserted.ID,… INTO @ .
. В ряде СУБД допустимо объявлять триггеры для нескольких действий одновременно. Для реализации разных реакций на различные действия в Oracle предусмотрены предикаты Deleting, Inserting, Updating, возвращающие True для соответствующего вида обновления.
. В СУБД Oracle можно для триггеров Update указать список столбцов (After Update Of), что обеспечит вызов триггера только при изменении значений только этих столбцов.
. Для каждого триггерного события может быть объявлено несколько триггеров (в Oracle 12 триггеров на таблицу) и обычно порядок их запуска определяется порядком создания. В некоторых СУБД, например, InterBase, порядок запуска указывается с помощью дополнительного ключевого слова POSITION . В общем случае считается, что первоначально должны выполняться триггеры для каждой команды, а затем – для каждой строки.
. Триггеры можно встраивать друг в друга. Так SQL Server допускает 32 уровня вложения (с помощью глобальной переменной @@NextLevel можно определить уровень вложения).

Недостатки триггеров

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

Изменение и удаление триггеров

Для удаление триггера используется оператор DROP TRIGGER
. Для изменения триггера используется оператор ALTER TRIGGER …
. Отключение триггеров
В ряде случаев, например, при пакетной загрузке, триггеры требуется отключать. В ряде СУБД предусмотрены соответствующие возможности. В Oracle и SQL Server ключевые слова DISABLE|ENABLE, в InterBase INACTIVE|ACTIVE в операторе ALTER TRIGGER.

Особенности промышленных серверов

1) InterBase/Firebird

CREATE TRIGGER FOR {ACTIVE|INACTIVE } {BEFORE|AFTER } {INSERT|DELETE|UPDATE } [POSITION ]
AS [DECLARE VARIABLE [()]]
BEGIN

END

Пример:

CREATE TRIGGER BF_Del_Cust FOR Customer
ACTIVE BEFORE DELETE POSITION 1 AS
BEGIN
DELETE FROM Orders WHERE Orders.CNum=Customer.CNum;
END;

2) SQL Server

CREATE TRIGGER ON [WITH ENCRYPTION ] {FOR|AFTER|INSTEAD OF } {INSERT|UPDATE|DELETE }
AS

USE B1;
GO
CREATE TRIGGER InUpCust1 ON Customer AFTER INSERT, UPDATE
AS RAISEERROR(‘Изменена таблица Customer’);

Дополнительные виды триггеров

В СУБД Oracle и SQL Server есть возможность создания (замещающих) триггеров для не обновляемых представлений. Для этого предусмотрены ключевые слова INSTEAD OF:

CREATE TRIGGER ON INSTEAD OF INSERT AS …

Можно отслеживать попытки клиента обновлять данные с помощью представлений и выполнять какие-либо действия, обрабатывать не обновляемые представления и т.п.
. В СУБД SQL Server предусмотрен триггер отката, фактически прекращающий все действия с выдачей сообщения:

ROLLBACK TRIGGER

SQL ,

Разработка веб-сайтов

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

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

Из работы над ними вынесли один ценный опыт - следить за приоритетами и статистикой. Что это значит? Все просто: если у Вас блог и у него 2-3-4-10012 млн посетителей в сутки, а статьи пишутся всего 1-2-3-3435 раз в сутки (на порядок меньше чем число просмотров), то скорость сохранения статьи (и сложность этого) относительно скорости показа статьи может быть пропорционально меньше. Чем больше показываем, тем критичен именно показ, а не сохранение статьи/страницы/таблицы. Что не означает, что и расслабляться можно. Сохранение статьи за 3-5-10 секунд в блоге - это в рамках адекватности, но генерация страницы за срок более 2 секунды (+ пока скрипты и стили с картинками подгрузятся) - это на грани «какой тормознутый сайт, почитаю что-то иное», а еще хуже «пойду куплю в другом месте».

Если мы возьмем среднестатистический сайт с голосовалкой/кармой, комментариями, счетчиком показа страницы и т.п., то многим разработчикам сразу в голову приходят конструкции вроде SELECT count(*) FROM comment WHERE comment.page=page_id. Ну подумаешь на каждую статью посчитать сумму рейтинга, сумму комментариев. А, у нас на главной по 10 статей из каждого раздела. При посещаемости в 10 человек в секунду, на среднем VPS, можно себе позволить по 60-100 запросов к sql на страницу (привет, битрикс).

Но к черту лирику (достал уже, наверное). Голые данные:

Таблица blog

CREATE TABLE IF NOT EXISTS `blog` (`id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(128) NOT NULL, `text` text NOT NULL, `creation` datetime NOT NULL, `modification` datetime NOT NULL, `img` varchar(128) NOT NULL DEFAULT "default.png", `status` tinyint(4) NOT NULL DEFAULT "2", `user_id` int(11) NOT NULL, `rate` int(11) NOT NULL, `relax_type` tinyint(4) NOT NULL, `timers` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `contest` tinyint(1) NOT NULL DEFAULT "0", `views` int(11) NOT NULL DEFAULT "0", `comment` int(11) NOT NULL, `url` varchar(128) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `url` (`url`), KEY `country_id` (`country_id`), KEY `user_id` (`user_id`), KEY `status` (`status`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1456435 ;

Таблица comments

CREATE TABLE IF NOT EXISTS `comments` (`owner_name` varchar(50) NOT NULL, `owner_id` int(12) NOT NULL, `id` int(12) NOT NULL AUTO_INCREMENT, `parent_id` int(12) DEFAULT NULL, `user_id` int(12) DEFAULT NULL, `text` text, `creation` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `status` int(1) NOT NULL DEFAULT "0", PRIMARY KEY (`id`), KEY `owner_name` (`owner_name`,`owner_id`), KEY `parent_id` (`parent_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=243254252 ;

Как видим, в таблице блога у каждой статьи есть счетчик комментариев (поле comment).
Обычная практика:
1. Добавили комментарий - увеличили счетчик для блога
2. Удалили/скрыли комментарий - уменьшили счетчик.
Делать это в коде удобно и привычно, но есть более удобный инструмент - триггеры.

И так, у нас есть 2 события (на самом деле 3): создание комментария и его удаление (третье событие - это изменение его статуса («удаление», бан и т. п.).
Рассмотрим только создание и удаление, а изменение статуса пусть будет домашним заданием.

В примере есть одна особенность: комментарии могут быть к нескольким типам статей.

Создание комментария:

CREATE TRIGGER `add_count_comment` AFTER INSERT ON `comments` FOR EACH ROW BEGIN // у пользователя в личном кабинете посчитаем сколько он комментариев написал UPDATE user SET user.countcomment= user.countcomment+1 WHERE user.id = NEW.user_id; // определяем к чему относится комментарий и сразу увеличиваем счетчик в данных таблицах CASE NEW.`owner_name` WHEN "Blog" THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`+1 WHERE `blog`.id = NEW.`owner_id` ; WHEN "Article" THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`+1 WHERE `article`.`id` = NEW.`owner_id` ; WHEN "PopulatePlace" THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`+1 WHERE `populate_place`.`id` = NEW.`owner_id` ; END CASE; // тут мы облегчаем себе работу с лентами новостей // url статьи сразу пишем, что бы ПОТОМ не делать выборок лишних CASE NEW.`owner_name` WHEN "Blog" THEN SET userurl = (SELECT url FROM `blog` WHERE `blog`.id= NEW.`owner_id`); WHEN "Article" THEN SET userurl = (SELECT url FROM `article` WHERE article.id=NEW.`owner_id`); WHEN "PopulatePlace" THEN SET userurl = ``; END CASE; // название статьи сразу пишем, что бы ПОТОМ не делать выборку CASE NEW.`owner_name` WHEN "Blog" THEN SET usertitle = (select title from `blog` where blog.id=NEW.`owner_id`); WHEN "Article" THEN SET usertitle = (select title from `article` where article.id=NEW.`owner_id`); WHEN "PopulatePlace" THEN SET usertitle = ` `; END CASE; INSERT INTO user_has_events VALUES (NEW.user_id,NEW.id,"Comments",NOW(),userurl , usertitle); END

Аналогично и удаление комментария:

CREATE TRIGGER `del_count_comment` AFTER DELETE ON `comments` FOR EACH ROW BEGIN UPDATE user SET user.countcomment= user.countcomment -1 WHERE user.id = OLD.user_id; CASE OLD.`owner_name` WHEN "Blog" THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`-1 WHERE `blog`.`id` = OLD.`owner_id` ; WHEN "Article" THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`-1 WHERE `article`.`id` = OLD.`owner_id` ; WHEN "PopulatePlace" THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`-1 WHERE `populate_place`.`id` = OLD.`owner_id` ; END CASE; END

И так, что получили:
1. При вставке комментария у нас автоматически средствами sql сервера посчиталась сумма комментариев у конкретного объекта комментирования (статья, страница, заметка)
2. Мы сформировали ленту новостей (привет всем соцсетям и т. п.)
3. При удалении комментария у нас происходит вычет всех данных.
4. Мы не использовали средства фреймворка.
5. Выборка всех нужных данных происходит быстро (всего 1 запрос при показе страницы, за исключением прочих «левых» данных на ней.)

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

Добавлен триггер:

CREATE TRIGGER `ins_blog` BEFORE INSERT ON `blog` // делаем вставку времени до сохранения информации путем «подмены» данных. FOR EACH ROW BEGIN SET NEW.modification = NOW(); END

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

CREATE TRIGGER `ins_blog` BEFORE UPDATE ON `blog` // делаем вставку времени до сохранения информации путем «подмены» данных. FOR EACH ROW BEGIN SET NEW.modification = NOW(); END

При изменении данных - обновим поисковый индекс тоже.

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

UPD: Холивар посвященный целесообразности усложнения структуры БД объявляется открытым.

Платформа SQL Server поддерживает ядро стандарта ANSI с добавлением триггеров типа INSTEAD OF и проверки изменения столбцов. Эта платформа не поддерживает предложения REFERENCING и WHEN. Синтаксис приводится ниже.

{CREATE | ALTER} TRIGGER имя_триггера ON имя_таблицы

{FOR | AFTER | INSTEAD OF} { [,] [,] }

[…]] блок_кода

(CREATE | ALTER) TRIGGER имя_триггера

Создается новый триггер с именем имя_триггера или изменяется существующий триггер имя_триггера путем добавления или изменения свойств триггера или блока кода. При изменении существующего триггера права доступа и зависимости существующего триггера сохраняются.

ON имя_таблицы

Объявляется таблица или представление, от которых зависит триггер. В представлениях могут быть определены триггеры INSTEAD OF, если только эти представления можно обновлять и они не содержат предложения WITH CHECK.

WITH ENCRYPTION

Текст инструкции CREATE TRIGGER шифруется так, как это определено в таблице syscomments. Этот параметр полезно использовать для защиты интеллектуальной собственности. Предложение WITH ENCRYPTION не дает использовать триггер в схеме репликации SQL Server.

FOR | AFTER INSTEAD OF

Указывает, когда должен запускаться триггер. (Ключевые слова FOR и AFTER являются синонимами.) Предложение AFTER показывает, что триггер запускается только после запуска успешного выполнения операции по модификации данных (и других каскадно запускаемых действий и проверок ограничений). Триггер INSTEAD OF сходен с триггером BEFORE стандарта ANSI в том, что код триггера может полностью заменить операцию по модификации данных. При этом триггер запускается вместо операции по модификации, которая запустила триггер. Триггеры типа INSTEAD OF DELETE нельзя использовать, если удаление вызывает каскадные действия. Доступ к столбцам TEXT, NTEXT или IMAGE имеют только триггеры INSTEAD OF.

WITH APPEND

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

NOT FOR REPLICATION

Предотвращается запуск триггера при выполнении операции по модификации данных, инициированной встроенной системой репликации SQL Server.

IF UPDATE (столбец) [{AND | OR) РОАТЕ(столбец)] […]

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

Платформа SQL Server позволяет использовать несколько триггеров для одной операции по манипуляции в таблице или представлении. Таким образом, можно использовать сразу три триггера UPDATE в одной таблице. В таблице можно использовать несколько триггеров AFTER. Порядок их выполнения является неопределенным, однако первый и последний триггер можно указать явным образом, при помощи хранимой системной процедуры spsettriggerorder. В любой таблице допускается использовать только один триггер INSTEAD OF на инструкцию INSERT, UPDATE или DELETE.

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

Платформа SQL Server неявно использует для триггеров форму FOR EACH STATEMENT стандарта ANSI.

При запуске триггера SQL Server записывает значения в две важные псевдотаблицы: deleted и inserted. Они соответственно эквивалентны псевдотаблицам before и after, описанным ранее в разделе «Общие правила», относящемся к стандарту ANSI. Эти таблицы по структуре идентичны таблице, в которой создан триггер, за исключением того, что они содержат старые данные, до выполнения операции по модификации (deleted), и новые значения, имеющиеся в таблице после этой операции (inserted).

При указании предложения AS IF UPDATE (столбец) производится проверка на выполнение операции INSERT или UPDATE в данном столбце или столбцах; это предложение аналогично конструкции с/РШЩстолбец) в стандарте ANSI. Можно указать несколько столбцов, добавив отдельные предложения (УРОЛЩстолбец). Если за предложением AS IF UPDATE (столбец) поставить блок кода Transact-SQL BEGIN…END, можно выполнить в триггере несколько операций Transact-SQL. Это предложение функционально эквивалентно операции IF … THEN … ELSE.

Помимо вмешательства в выполнение операций по модификации данных, показанного в примере на ANSI SQL, платформа SQL Server позволяет выполнять другие виды операций при операции по модификации данных. В следующем примере мы решили, что таблица sales_archive_2002 более не используется, и тот, кто попытается вставить в нее данные, получит уведомление о данном ограничении.

Платформа SQL Server не разрешает использовать следующие инструкции в блоке кода Transact-SQL триггера: ALTER, CREATE, DROP, DENY, GRANT, REVOKE, LOAD, RESTORE, RECONFIGURE и TRUNCATE. Кроме того, не разрешаются инструкции DISK и команда UPDA ТЕ STA TISTICS.

Платформа SQL Server позволяет запускать рекурсивные триггеры, используя параметр recursive triggers хранимой системной процедуры sp_dboption. Рекурсивные триггеры в результате своего выполнения запускают самих себя. Например, если триггерINSERT в таблице Т1 выполняет операцию INSERT в таблице Т1, он может выполнить рекурсивную операцию. Поскольку рекурсивные триггеры могут быть опасными, они по умолчанию отключены.

Также SQL Server позволяет использовать вложенные триггеры, до 32 уровней вложенности. Если любой из вложенных триггеров выполняет операцию ROLLBACK, то последующие триггеры не запускаются. Пример вложенных триггеров: Триггер в таблице Т1 запускает операцию над таблицей Т2, в которой также есть триггер, который запускает операцию над таблицей ТЗ. Запуск триггеров отменяется, если формируется бесконечный цикл. Вложенные триггеры можно разрешить при помощи параметра nested triggers хранимой процедуры sp_configure. Если вложенные триггеры отключены, то рекурсивные триггеры также отключены, независимо от соответствующего параметра хранимой процедуры sp_dboption.

В следующем примере мы хотим переадресовать пользовательские действия над таблицей people, особенно транзакции, связанные с обновлением, таким образом, чтобы изменения строк таблицы people записывались вместо этого в таблицу peoplejreroute. (Более сложная форма таблицы people показана в пункте «SQL Server» раздела «Инструкция CREATE/ALTER TABLE».) Наш триггер обновлений будет регистрировать все изменения столбцов 2, 3 и 4 таблицы people и записывать их в таблицу peoplejreroute. Также триггер будет записывать, какой пользователь выполнял транзакцию-обновление и в какое время.

Отметьте, что инструкции CREATE в SQL Server позволяют использовать отложенное разрешение имен (deferred name resolution). Это означает, что команда обрабатывается даже в том случае, если она ссылается на еще не существующий объект базы данных.

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

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.

1. Запустите SQL Server Enterprise Manager.
2. Щелкните правой кнопкой мыши на таблице, для которой хотите создать триггер, и в контекстном меню выберите команду Task > Manage Triggers . В результате этих действий появится диалоговое окно, в котором можно ввести текст триггера и присвоить ему имя.
3. После окончания ввода можно выполнить проверку синтаксиса и нажать кнопку 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, выполните следующие действия:

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

Системные хранимые процедуры 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 % как редкой и ценной.

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