Вложенные и рекурсивные вызовы триггеров

Если триггер обновляет таблицу, это обновление может вызвать выполнение другого триггера или даже того же самого. По умолчанию SQL Server допускает до 32 вложенных не рекурсивных вызовов триггеров, а рекурсивные вызовы запрещает. Мы же рекомендуем разрешить рекурсивное выполнение триггеров и пользоваться им по мере необходимости. Опция, разрешающая рекурсивное выполнение триггеров, устанавли­вается с помощью инструкции ALTER DATABASE или системной хранимой процедуры sp_dboption:

ALTER DATABASE AppDta

SET RECURSIVE_TRIGGERS ON

или

sp_dboption AppDta ‘recursive triggers’, ‘true’

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

sp_configure ‘nested triggers’, 0

Вывод информации о триггерах

Информацию о триггерах возвращают четыре системные хранимые процедуры:

ü sphelp имя-триггера — возвращает имя владельца триггера, а также дату и время его создания;

ü sphelptext имя-триггера — возвращает исходный код триггера (если он не зашифрован);

ü sp_depends имя-триггера — возвращает список объектов, на которые в триггере имеются ссылки;

ü sphelptrigger имя-таблицы — возвращает список триггеров, определенных для заданной таблицы.

Процедура sphelptext выручит вас, если вы потеряли исходный код одного из триггеров.

Программирование триггеров

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

ALTER DATABASE CREATE DATABASE DISK INIT

DISK RESIZE DROP DATABASE LOAD DATABASE

LOAD LOG RECONFIGURE RESTORE DATABASE

RESTORE LOG

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

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

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

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

CREATE TRIGGER CheckDiscountUpdates

ON AppDta.dbo.Customer FOR INSERT, UPDATE

AS

-- Проверяем, задан ли в инструкции столбец Discount

IF UPDATE(Discount) BEGIN

DECLARE @User VARCHAR(256) SET NOCOUNT ON

-- Проверяем, действительно ли задано значение столбца

-- Discount

IF (EXISTS (SELECT * FROM INSRETED WHERE Discount > 0))

BEGIN

-- Убеждаемся, что пользователь имеет право назначать

-- скидки

IF (NOT EXISTS (SELECT * FROM Employee

WHERE UserName = CURRENT_USER

AND DiscountAuthority = ‘Y’))

BEGIN

-- Отправляем сообщение и отменяем транзакцию

SET @User = CURRENT_USER

RAISERROR(‘User %s not approved to assign dis-

counts.’, 16, 1, @User )

ROLLBACK

END

END

END

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

Поскольку инструкция должна удовлетворять всем ограничениям таблицы еще до вызова триггера, триггеры нельзя использовать для обеспечения выполнения этих ограничений. Предположим, например, что для таблицы Sale определено ограничение внешнего ключа без каскадного удаления. Это ограничение препятствует удалению строки таблицы Customer, если с ней связана хоть одна строка таблицы Sale. Возможно, вы захотите создать DELEТЕ-триггер для таблицы Customer, удаляющий из таблицы Sale все заказы удаляемого клиента, сделанные в прошлом году, и тем самым разрешающий удаление строки таблицы Customer, если больше заказов не осталось. Но это не так просто. Поскольку, как уже говорилось, ограничение внешнего ключа проверяется еще до вызова триггера, SQL Server 2000 просто отменит операцию удаления клиента, имеющего хоть один заказ. В итоге ваш триггер даже не будет вызван. Впрочем, у этой проблемы все же имеется решение. Таким решение является использование замещающих триггеров, о которых рассказывается в следующем разделе.

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

Замещающие триггеры

До сих пор мы говорили об обычных триггерах (after-триггерах), т.е. триггерах, выполняемых после операции, с которой они связаны, и после проверки всех относящихся к этой операции ограничений. Триггеры другого типа, называемые замещающими триггерами (instead of triggers), выполняются вместо операции, с которой они связаны. Замещающие триггеры могут выполнять множество действий, причем не обязательно связанных с исходной операцией (например, добавлением строк в таблицу). И если обычные триггеры определяются исключительно для базовых таблиц (поскольку именно там модифицированы данные, с которыми они работают), то замещающие триггеры можно определять и для представлений, причем даже для тех представлений, которые без триггеров были бы доступны только для чтения.

Для создания замещающего триггера ключевое слово for (или after) в инструкции create trigger нужно заменить ключевым словом instead of. Ниже приведен пример замещающего триггера, который решает описанную в предыдущем разделе проблему: автоматически удаляет зависимые строки таблицы Sale при удалении родительской строки таблицы Customer:

CREATE TRIGGER CustomerDelete ON Customer INSTEAD OF DELETE

AS SET NOCOUNT ON

-- Проверяем, имеются ли в таблице Sale прошлогодние заказы

-- удаляемых клиентов

IF (EXISTS (SELECT * FROM DELETED JOIN Sale

ON DELETED.Custld = Sale.Custld

WHERE DATEDIFF (DAY, SaleDate,

CURRENT__TIMESTAMP) < 365)) BEGIN

RAISERROR(‘One or more customers have recent sales’, 16, 1)

RETURN

END

-- Удаляем соответствующие строки таблицы Sale

-- (все они устарели)

DELETE FROM Sale WHERE Custld IN (SELECT Custld FROM DELETED)

IF (@@ERROR > 0) BEGIN

ROLLBACK

RAISERROR (‘Could not delete all old sales’, 16, 2) RETURN

END

-- Удаляем клиентов

DELETE FROM Customer WHERE Custld IN

(SELECT Custld FROM DELETED )

-- В случае неудачи отменяем все изменения

IF (@@ERROR > 0) BEGIN

ROLLBACK

RAISERROR(‘Could not delete all old customers’, 16, 3)

RETURN

END

RETURN

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

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

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

Задание к работе

Создать триггеры с помощью Enterprise Manager, Query Analyzer и MS Access для ведения учета изменения данных в таблицах.