Поделиться через

  • Статья

Использование таблиц 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.

 
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.VendorID = p.VendorID
           WHERE v.CreditRating = 5
          )
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;

-- 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, заданной для представления. Например:

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