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

Изменения: 17 июля 2006 г.

Инструкции триггеров DML используют две особые таблицы: deleted (удаленные значения) и inserted (вставленные значения). SQL Server 2005 автоматически создает и управляет ими. Эти временные таблицы, находящиеся в оперативной памяти, используются для проверки результатов изменений данных и для установки условий срабатывания триггеров 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 2005 не позволяет ссылаться на столбцы типов 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.

IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
CREATE TRIGGER LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint,
   @vendorid int
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader AS p 
    INNER JOIN inserted AS i ON p.PurchaseOrderID =
   i.PurchaseOrderID 
    JOIN Purchasing.Vendor AS v on v.VendorID = i.VendorID
IF @creditrating = 5
BEGIN
   RAISERROR ('This vendor''s credit rating is too low to accept new
      purchase orders.', 16, 1)
ROLLBACK TRANSACTION
END
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, компонент SQL Server 2005 Database Engine вызывает триггер вместо того, чтобы предпринять какое-либо прямое действие по отношению к таблице. Триггер использует сведения, представленные в таблицах inserted и deleted, для создания инструкций, необходимых для выполнения требуемых действий в базовых таблицах, даже в том случае, если формат данных в таблицах inserted и deleted, созданных для представления, отличается от формата данных базовой таблицы.

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

CREATE VIEW EmployeeNames (EmployeeID, LName, FName)
AS
SELECT e.EmployeeID, c.LastName, c.FirstName
FROM AdventureWorks.HumanResources.Employee e 
JOIN AdventureWorks.Person.Contact c
ON e.ContactID = c.ContactID

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

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

См. также

Основные понятия

Реализация триггеров DML

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений

Версия	Журнал
17 июля 2006 г.	Новое содержимое Добавлен пример, показывающий применение таблицы inserted в триггере для выполнения бизнес-правил.