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

0

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

Организация триггеров

В версии SQL Server 6.5 каждое событие могло иметь только один триггер, а один триггер мог применяться только к одному событию. Стиль программирования, обязующий использовать только такие ограниченные триггеры, показал свою несостоятельность. В версии 7.0 SQL Server реализована возможность существования множества триггеров AFTER для одного события таблицы, при этом один триггер может применяться к нескольким событиям. Это позволило использовать более гибкий стиль разработки приложений.

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

т                                                                    ? изменение даты;

?               проверка данных

?               поддержка сложных правил бизнес-логики ? обеспечение сложной защиты данных.

?               ведение журнала аудита

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

Термин вложение триггеров указывает на то, могут ли триггеры, вызываемые инструкциями DML, вызывать другие триггеры. Например, если параметр сервера Nested Triggers включен и некоторый триггер обновляет таблицу А, а эта таблица сама имеет триггер, то и он тоже будет вызван (рис. 23.2).

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

ЕХЕС sp_configure ‘Nested Triggers’, 1 Reconfigure

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

Триггеры в SQL Server ограничены 32 уровнями рекурсии. Из соображений безопасности рекомендуется проверять текущий уровень рекурсии в самом триггере — для этого предназначена функция Trigger_NestLevel (). Когда предел будет достигнут, SQL Server генерирует фатальную ошибку.

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

Рис. 23.3. Рекурсивный триггер ссьтается сам на себя. Он выполняет инструкцию DML, которая запускает его повторно

Рекурсивный триггер является уникальным типом вложенного триггера AFTER. Если триггер выполняет инструкцию DML, которая снова вызывает его самого, он называется рекурсивным (рис. 23.3). Если параметр базы данных Recursive_Triggers отключен, то рекурсивная итерация триггеров становится невозможной.

Рис. 23.2. Параметр конфигурации Nested Triggers позволяет инструкции DML в теле триггера инициировать еще один триггер

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

Рекурсивные триггеры включаются с помощью инструкции ALTER DTATBASE:

ALTER DATABASE имя_базы_данных SET RECURSIVE_TRIGGERS ON | OFF

В качестве примера полезного рекурсивного триггера рассмотрим триггер Modif iedData. Он записывает текущие дату и время в изменяемый столбец обновляемой строки. Используя учебную базу данных OBXKites в качестве модели, добавим в таблицу товаров столбцы Created и Modified:

USE OBXKites ALTER TABLE dbo.Product ADD

Created DateTime Not Null DEFAULT GetDate(),

Modified DateTime Not Null DEFAULT GetDateO

Представленный триггер вначале выводит свой уровень вложенности, используя функцию TriggerNest (). Это очень полезно в целях отладки во вложенных или рекурсивных триггерах. Первый оператор if предохраняет столбцы Created и Modified от ручного изменения пользователем. Если триггер инициируется пользователем, уровень вложенности будет равен единице.

Первый раз триггер инициируется инструкцией UPDATE. Все последующие запуски триггера завершаются оператором Return, поскольку уровень вложенности превышает единицу. Это позволяет избежать бесконечной рекурсии. Ниже приведен код DDL этого триггера. CREATE TRIGGER Products_ModifiedDate ON dbo.Product FOR UPDATE AS

SET NoCount ON

PRINT Trigger_NestLevel()

If Trigger_NestLevel() > 1 Return

If (Update(Created) or Update(Modified))

AND Trigger_NestLevel() =1 Begin

Raiserror(1 Обновление отключено.1, 16, 1)

Rollback

Return

End

– Обновление даты изменения строки UPDATE Product

SET Modified = getdate()

FROM Product JOIN Inserted

ON Product.ProductID = Inserted.ProductID

Для тестирования триггера выполним следующую инструкцию UPDATE, что приведет к изменению триггером значения столбца Modified. После этого инструкция SELECT возвращает дату и время из столбцов Created и Modified:

UPDATE PRODUCT

SET [Name] = ‘Modified Trigger’

WHERE Code = ‘1002’

SELECT Code, Created, Modified

FROM Product

WHERE Code = ‘1002’

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

Code Created                                       Modified

1002       2002-02-18 09:48:31.700 2002-02-18 15:19:34.350

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

На заметку

Триггеры INSTEAD OF И AFTER

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

1.              Инструкция DML инициирует транзакцию.

2.              Вместо инструкции DML выполняется триггер INSTEAD OF.

3.              Если триггер INSTEAD OF выполняет инструкцию DML для того же события таблицы, то процесс продолжается.

4.              Выполняется триггер AFTER.

Множество триггеров after

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

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

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

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

sp_settriggerorder

@triggername = ‘имя_триггера1,

@order = ’first’ или ‘last’ или ‘none’,

@stmttype = ‘INSERT’ или ‘UPDATE’ или ‘DELETE’

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

Резюме

Триггеры являются ключевой функцией в базах данных с архитектурой “клиент/сервер”, позволяют разработчику создавать сложные правила бизнес-логики и гарантировать их строгое выполнение на уровне базы данных. SQL Server 2005 содержит два типа триггеров: AFTER и INSTEAD OF.

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

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

По теме:

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