Использование таблиц inserted и deleted

Инструкции триггеров DML используют две особые таблицы: Таблицы deleted и inserted. SQL Server автоматически создает эти таблицы и управляет ими. Эти временные таблицы, находящиеся в оперативной памяти, используются для проверки результатов изменений данных и для установки условий срабатывания триггеров DML. Нельзя в этих таблицах изменять данные напрямую или выполнять над ними операции языка DDL, например инструкцию CREATE INDEX.

В триггерах DML таблицы inserted и deleted в основном используются для выполнения следующих операций.

• Расширение ссылочной целостности между таблицами.

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

• Проверка на ошибки и принятие соответствующих мер в связи с появлением ошибок.

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

В таблице deleted находятся копии строк, с которыми работали инструкции DELETE или UPDATE. При выполнении инструкции DELETE или UPDATE происходит удаление строк из таблицы триггера и их перенос в таблицу deleted. У таблицы deleted обычно нет общих строк с таблицей триггера.

В таблице inserted находятся копии строк, с которыми работали инструкции INSERT или UPDATE. При выполнении транзакции вставки или обновления происходит одновременное добавление строк в таблицу триггера и в таблицу inserted. Строки таблицы inserted являются копиями новых строк таблицы триггера.

Транзакция обновления аналогична выполнению операции удаления с последующим выполнением операции вставки; сначала старые строки копируются в таблицу deleted, а затем новые строки копируются в таблицу триггера и в таблицу inserted.

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

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

SQL Server 2008 не позволяет ссылаться на столбцы типов text, ntext и image в таблицах inserted и deleted триггеров AFTER. Однако эти типы данных включены в целях обратной совместимости. Предпочтительным способом хранения для данных большого объема является использование типов данных varchar(max), nvarchar(max) и varbinary(max). Как триггеры AFTER, так и триггеры INSTEAD OF поддерживают данные типов varchar(max), nvarchar(max) и varbinary(max) в таблицах inserted и deleted. Дополнительные сведения см. в разделе CREATE TRIGGER (Transact-SQL).

Примеры использования таблицы inserted в триггере для выполнения бизнес-правил

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

В следующем примере создается триггер DML. Этот триггер проверяет уровень кредитоспособности поставщика при попытке добавить новый заказ на покупку в таблицу PurchaseOrderHeader. Чтобы получить оценку кредитоспособности поставщика, связанного с только что добавленным заказом на покупку, таблица inserted должна ссылаться на таблицу Vendor и быть связана с ней. В случае слишком низкой кредитоспособности выводится соответствующее сообщение и вставка не производится.

Примечание
Примеры триггеров DML AFTER, которые обновляют несколько строк, см. в разделе Замечания по работе с несколькими строками в триггерах DML.

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