Использование таблиц вставленных и удаленных данных

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

Основные сведения о таблицах вставленных и удаленных данных

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

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

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

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

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

Таблица удаленных данных deleted хранит в таблице триггера копии измененных строк перед тем, как они были изменены инструкцией DELETE или UPDATE (таблица триггера — это таблица, в которой выполняется триггер DML). Во время выполнения инструкции DELETE или UPDATE измененные строки сначала копируются из таблицы триггера и переносятся в таблицу удаленных данных.

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

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

1. Исходная строка копируется из таблицы триггера в таблицу удаленных данных.
2. Таблица триггера обновляется новыми значениями из инструкции UPDATE.
3. Обновленная строка таблицы триггера копируется в таблицу вставленных данных.

Это позволяет сравнить содержимое строки перед обновлением (в таблице удаленных данных) со новыми значениями строки после обновления (в таблице вставленных данных).

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

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

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

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

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

USE AdventureWorks2022;
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 (ROWCOUNT_BIG() = 0)
RETURN;
IF EXISTS (SELECT 1  
           FROM inserted AS i   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = i.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

Использование таблиц вставленных и удаленных данных в триггерах 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, ядро СУБД вызывает триггер вместо того, чтобы выполнять какие-либо прямые действия с любой таблицей. Триггер использует сведения, представленные в таблицах inserted и deleted, для создания инструкций, необходимых для выполнения требуемых действий в базовых таблицах, даже в том случае, если формат данных в таблицах inserted и deleted, созданных для представления, отличается от формата данных базовой таблицы.

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

USE AdventureWorks2022;  
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 в представлении должен обрабатывать такие типы выражений.

Замечания, связанные с быстродействием

Так как вставленные и удаленные таблицы являются виртуальными резидентными таблицами, такие свойства, как статистика или индексы, недоступны. Хотя в этих таблицах представлены сведения о кратности, следует соблюдать осторожность при расчете числа строк, которые должны временно храниться. Вставка большого числа строк в эти таблицы и выполнение запросов к ним или их объединение с другими таблицами может привести к появлению неоптимальных планов запросов и медленному выполнению запросов. Тщательно разработайте и протестируйте приложение с учетом требований к производительности запросов.

Следующие шаги

Дополнительные сведения см. в обзоре триггеров DML.