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

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureБаза данных SQL в Microsoft Fabric

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

Понимание вставленных и удаленных таблиц

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

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

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

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

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

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

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

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

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

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

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

SQL Server не разрешает ссылки на столбцы text, ntext или image в вставленных и удаленных таблицах для триггеров 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 . Чтобы получить кредитный рейтинг поставщика, соответствующего только что добавленному заказу на покупку, таблица 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
        INNER 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;
    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 прямо сопоставляется с соответствующим столбцом базовой таблицы.

Следующие правила, касающиеся того, когда INSERTUPDATE оператор или, ссылающийся на таблицу с триггером ВМЕСТО OF, должен придавать значения столбцов, совпадают с тем, что если бы в таблице не было триггера ВМЕСТО OF:

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

• Значения не могут быть указаны для столбцов с определённым IDENTITY свойством, если IDENTITY_INSERT только для этой таблицы не включено. Когда IDENTITY_INSERT ВКЛЮЧЕНО, INSERT операторы должны предоставлять значение.

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

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

Когда INSERTоператор , UPDATE, или DELETE ссылается на представление с триггером ВМЕСТО 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.