Создание и управление индексами. Создание и управление встроенными процедурами. Оптимизация производительности запросов. Управление транзакциями и блокировками, страница 24

ON P.ProductID=d.ProductID

Как работает триггер UPDATE

Обновление происходит в два этапа – удаление и вставка, поэтому существующие строки помещаются в таблицу deleted, а новые данные помещаются в таблицу inserted. Триггер может проверять эти таблицы для определения, какие строки и как могут измениться.

Вы можете объявить триггер для мониторинга обновления определённой колонки с помощью указания опции IF UPDATE. Это позволяет триггеру изолировать активность определённой колонки. Когда обнаруживается обновление определённой колонки, триггер может выполнить определённые действия, такие как выброс сообщения об ошибке, которое сообщит о невозможности обновления колонки.

Пример

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

USE Northwind

GO

CREATE TRIGGER Employee_Update ON Employees

FOR UPDATE

AS

IF UPDATE (EmployeeID)

BEGIN TRANSACTION

RAISERROR(‘ID работника не может быть изменено’, 10, 1)

ROLLBACK TRANSACTION

END

Как работает триггер INSTEAD OF

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

Пример

Следующий пример создаёт таблицу с заказчиками из Германии и с заказчиками из Мексики. Триггер INSTEAD OF помещается на просмотрщик, перенаправляя обновления на соответствующую основную таблицу.

SELECT * INTO Customersger

FROM Customers

WHERE Customers.Country='Germany'

SELECT * INTO CustomersMex

FROM Customers

WHERE Customers.Country='Mexico'

Создание просмотрщик на эти данные:

CREATE VIEW CustomersView

AS

SELECT * FROM CustomersGer

UNION

SELECT * FROM CustomersMex

Следующий пример создаёт INSTEAD OF триггер на просмотрщик:

CREATE TRIGGER Customers_Update2

ON CustomersView

INSTEAD OF UPDATE

AS

DECLARE @Country nvarchar(15)

SET @Country = (SELECT Country FROM Inserted)

IF @Country='Germany'

BEGIN

UPDATE CustomersGer

SET CustomersGer.Phone=Inserted.Phone

FROM CustomersGer JOIN Inserted

ON CustomersGer.CustomerID=Inserted.CustomerID

END

ELSE IF @Country='Mexico'

BEGIN

UPDATE CustomersMex

SET CustomersMex.Phone=Inserted.Phone

FROM CustomersMex JOIN Inserted

ON CustomersMex.CustomerID=Inserted.CustomerID

END

Следующий код тестирует работу триггера:

UPDATE CustomersView SET Phone = '030-007xxxx'

WHERE CustomerID='ALFKI'

SELECT CustomerID, Phone

FROM CustomersView

WHERE CustomerID='ALFKI'

SELECT CustomerID, Phone

FROM CustomersGer

WHERE CustomerID='ALFKI'

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

Любой триггер может содержать операторы UPDATE, INSERT или DELETE, которые воздействуют на другие таблицы. С включённым вложением, триггер, который изменяет таблицу, может активировать другой триггер, который по очереди может активировать третий и так далее. Вложение при инсталляции включено, но вы можете отключить и снова включить с помощью системной процедуры sp_configure.

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

  Вложение по умолчанию включено;

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

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

Для проверки уровня вложения вы можете использовать функцию @@NESTLEVEL.

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