Использование таблиц 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 2012 не позволяет ссылаться на столбцы типов 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 для обработки нескольких строк данных.

USE AdventureWorks2012;
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

Использование таблиц inserted и deleted в триггерах INSTEAD OF

Таблицы inserted и deleted в триггерах INSTEAD OF подчиняются тем же правилам, что и таблицы inserted и deleted в триггерах AFTER. Формат таблиц inserted и deleted совпадает с форматом таблицы, для которой задан триггер INSTEAD OF. Каждый столбец таблиц inserted и deleted прямо сопоставляется с определенным столбцом базовой таблицы.

Следующие правила относятся к инструкциям INSERT или UPDATE, ссылающимся на таблицу с триггером INSTEAD OF, которые должны предоставлять такие значения для столбцов, как если бы в таблице не было триггера INSTEAD OF.

  • Не могут быть заданы значения для вычисляемых столбцов и для столбцов с типом данных timestamp.

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

  • Инструкции INSERT должны определять значения для всех столбцов со свойством NOT NULL, не имеющих ограничений DEFAULT.

  • Для любого столбца, кроме вычисляемых столбцов, столбцов определителя и столбцов типа timestamp, определение значений является необязательным, если разрешены NULL значения или если какой-либо столбец со свойством NOT NULL обладает определением DEFAULT.

Если инструкция INSERT, UPDATE или DELETE ссылается на представление, для которого определен триггер INSTEAD OF, компонент Компонент Database Engine вызывает триггер вместо того, чтобы предпринять какое-либо прямое действие по отношению к таблице. Триггер использует сведения, представленные в таблицах inserted и deleted, для создания инструкций, необходимых для выполнения требуемых действий в базовых таблицах, даже в том случае, если формат данных в таблицах inserted и deleted, созданных для представления, отличается от формата данных в базовых таблицах.

Формат таблиц inserted и deleted триггера INSTEAD OF, заданного для представления, совпадает со списком выборки инструкции SELECT, заданной для представления. Например:

USE AdventureWorks2012;
GO
CREATE VIEW dbo.EmployeeNames (BusinessEntityID, LName, FName)
AS
SELECT e.BusinessEntityID, p.LastName, p.FirstName
FROM HumanResources.Employee AS e 
JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID;

Результирующий набор для данного представления состоит из трех столбцов: одного столбца типа int и двух столбцов типа nvarchar. Таблицы inserted и deleted триггера INSTEAD OF, заданного для представления, также содержат столбец типа int с именем BusinessEntityID, столбец типа nvarchar с именем LName и столбец типа nvarchar с именем FName.

Список выборки представления также может содержать выражения, которые не сопоставлены напрямую с каким-либо одним столбцом базовой таблицы. Некоторые выражения представления, такие как вызов функции или константы, могут не ссылаться на столбцы и просто пропускаться. Сложные выражения могут ссылаться на несколько столбцов, однако таблицы inserted и deleted содержат только по одному значению для каждой вставляемой строки. Такие же проблемы появляются и в простых выражениях представления, если они ссылаются на вычисляемый столбец со сложным выражением. Триггер INSTEAD OF в представлении должен обрабатывать такие типы выражений.