使用 inserted 與 deleted 資料表

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

DML 觸發程序陳述式會使用兩種特殊的資料表：「deleted」資料表和「inserted」資料表。 SQL Server 會自動建立並管理這些資料表。 您可以使用這些暫存、常駐記憶體的資料表來測試某些資料修改的效果，以及設定 DML 觸發程序動作的條件。 您無法直接修改這些資料表的資料，或是在這些資料表上執行資料定義語言 (DDL) 作業，例如 CREATE INDEX。

了解 inserted 與 deleted 資料表

在 DML 觸發程序中，inserted 和 deleted 資料表主要是用來執行下列動作：

  • 擴充資料表之間的參考完整性。

  • 在檢視底下的基底資料表中插入或更新資料。

  • 測試錯誤，並根據錯誤採取動作。

  • 尋找資料修改前後之資料表狀態的差異，並依據這些差異採取動作。

deleted 資料表在接受 DELETE 或 UPDATE 陳述式的變更前，會先儲存觸發程式資料表中受影響資料列的複本 (觸發程式資料表是指執行 DML 觸發程式的資料表)。 執行 DELETE 或 UPDATE 陳述式的期間，系統會先複製觸發程序資料表中的受影響資料列，然後傳送到 deleted 資料表。

inserted 資料表會在 INSERT 或 UPDATE 陳述式之後儲存新資料列或變更資料列的複本。 執行 INSERT 或 UPDATE 陳述式的期間，系統會將觸發程序資料表中的新資料列或變更資料列複製到 inserted 資料表。 inserted 資料表中的資料列即為觸發程序資料表中新資料列或變更資料列的複本。

更新交易類似於先執行刪除作業，再執行插入作業。 執行 UPDATE 陳述式的期間，會依序發生下列事件：

  1. 原始資料列從觸發程序資料表複製到 deleted 資料表。
  2. 觸發程序資料表更新為 UPDATE 陳述式中的新值。
  3. 觸發程式資料表中更新的資料列複製到 inserted 資料表。

您可以藉此比較更新前的資料列內容 (在 deleted 資料表中) 與更新後的新資料列值 (在 inserted 資料表中)。

設定觸發程序的條件時，可以適當地利用 inserted 與 deleted 資料表設定引發觸發條件的動作。 雖然參考 deleted 資料表來測試 INSERT 陳述式或是參考 inserted 資料表測試 DELETE 陳述式時，並不會有任何錯誤發生，但在這種情形下，觸發程序測試資料表內將不會有任何資料列產生。

注意

如果觸發程序動作依據被修改的資料列數目來決定啟動與否時，則可利用對多資料列資料修改 (依據 SELECT 陳述式的 INSERT，DELETE 或 UPDATE) 的測試 (如 @@ROWCOUNT 的檢查)，以決定執行何者動作。 如需詳細資訊，請參閱 建立 DML 觸發程序以處理多重資料列。

SQL Server 不允許 AFTER 觸發程序的 inserted 與 deleted 資料表中的 text、ntext，或 image 資料行參考。 但還是包含這些資料類型，僅做為回溯相容性的目的使用。 大型資料的慣用儲存體應使用 varchar(max) 、 nvarchar(max) ，以及 varbinary(max) 資料類型。 AFTER 和 INSTEAD OF 兩個觸發程序都支援已插入及已刪除資料表中的 varchar(max) 、 nvarchar(max) ，和 varbinary(max) 資料。 如需詳細資訊，請參閱 CREATE TRIGGER (TRANSACT-SQL)。

範例：在觸發程序中使用 inserted 資料表強制執行商務規則

由於 CHECK 條件約束只能參考定義了資料行層級或資料表層級條件約束的資料行，任何跨資料表的條件約束 (這裡是商務規則) 都必須定義成觸發程序。

下列範例會建立一個 DML 觸發程序。 當試圖在 PurchaseOrderHeader 資料表中插入新的採購單時，這個觸發程序會檢查確認供應商的信用等級良好。 若要取得對應至剛插入之採購單的供應商信用等級，您必須參考 Vendor 資料表，而此資料表也必須與 inserted 資料表聯結。 如果信用等級太低，將會顯示訊息，且不會執行插入動作。

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 資料表遵循相同的規則。 inserted 及 deleted 資料表的格式，與據以定義 INSTEAD OF 觸發程序的資料表格式相同。 inserted 及 deleted 資料表中的每一資料行，都會直接對應到基底資料表中的資料行。

下列規則是有關參考含 INSTEAD OF 觸發程序之資料表的 INSERT 或 UPDATE 陳述式何時必須提供資料行值，即使資料表不含 INSTEAD OF 觸發程序時也一樣：

  • 無法為計算資料行或資料類型為 timestamp 的資料行指定值。

  • 除非資料表的 IDENTITY_INSERT 是 ON，否則無法為帶有 IDENTITY 屬性的資料表指定值。 當 IDENTITY_INSERT 為 ON 時，INSERT 陳述式必須提供一個值。

  • INSERT 陳述式必須為不含 DEFAULT 條件約束的所有 NOT NULL 資料行提供值。

  • 除了計算、識別或 時間戳記 資料行之外，只要是任何允許 Null 值的資料行，或含 DEFAULT 定義的任何 NOT NULL 資料行，其值都是有選擇性的。

當 INSERT、UPDATE 或 DELETE 陳述式參考含有 INSTEAD OF 觸發程序的檢視時，資料庫引擎會呼叫觸發程序，而不會對任何資料表採取直接動作。 觸發程序必須使用 inserted 及 deleted 資料表中的資訊，來建立實作基底資料表中要求的動作所需的陳述式，即使為檢視所建立的 inserted 及 deleted 資料表中的資訊格式與基底資料表中的資料格式並不相同也一樣。

傳遞給定義在檢視上 INSTEAD OF 觸發程序的 inserted 及 deleted 資料表格式，必須與針對檢視定義的 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 資料行。 傳遞至檢視所定義的 INSTEAD OF 觸發程序的已插入或已刪除的資料表，也有一個名為 的 int BusinessEntityID資料表、一個名為 的 nvarchar LName資料表，以及一個名為 的 nvarchar FName資料表。

檢視的選取清單也可以包含不直接對應到單一基底資料表資料行的運算式。 有些檢視運算式，例如條件約束或函數引動過程，無法參考任何資料行且可以被忽略。 複雜運算式可以參考多個資料行，但 inserted 及 deleted 資料表的每個插入資料列只能有一個值。 同樣的原則亦適用於檢視中的簡單運算式，前提是它們參考的計算資料行含有複雜運算式。 檢視的 INSTEAD OF 觸發程序必須處理這些類型的運算式。

效能考量

因為 inserted 與 deleted 資料表是虛擬的記憶體駐留資料表，所以無法使用統計資料或索引等屬性。 雖然這些資料表的部分基數資訊會公開，您還是務必要審慎思考暫時儲存在該處的資料列數目。 在這些資料表中插入大量資料列，然後進行查詢或與其他資料表聯結，可能會導致查詢計劃無法得到最理想的結果，並減緩查詢執行速度。 請務必仔細設計和測試應用程式，以滿足您的查詢效能需求。

下一步

如需詳細資訊，請參閱 DML 觸發程序概觀。