使用「插入」和「刪除」資料表

2025-03-05

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

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

了解已插入與已刪除的資料表

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

擴充資料表之間的參考完整性。
在檢視底下的基底資料表中插入或更新資料。
測試錯誤，並根據錯誤採取動作。
尋找資料修改前後之資料表狀態的差異，並依據這些差異採取動作。

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

inserted 資料表在 INSERT 或 UPDATE 語句執行後，會儲存新資料列或變更後資料列的複本。執行 INSERT 或 UPDATE 陳述式期間，系統會將觸發器資料表中的新資料列或變更後的資料列複製到插入資料表中。在插入的資料表中，資料列是觸發程序資料表中新增加或更新的資料列的複本。

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

原始資料列從觸發器資料表複製到 "deleted" 資料表。
觸發器表已更新為來自 UPDATE 陳述式的新值。
觸發程式資料表中更新的資料列被複製到已插入資料表。

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

當您設定觸發程序條件時，應適當使用 inserted 及 deleted 資料表來配合觸發程式的執行。雖然在測試 INSERT 陳述式時參考 deleted 資料表，或在測試 DELETE 陳述式時參考 inserted 資料表，並不會導致任何錯誤，但在這些情況下，觸發器測試資料表中將不會有任何資料列存在。

注意

如果觸發程序動作依賴資料修改所影響的列數來決定啟動，您可以使用適當的測試（例如檢查 @@ROWCOUNT）來處理基於 SELECT 陳述式的多行資料修改（如 INSERT、DELETE 或 UPDATE），以採取相應的動作。如需詳細資訊，請參閱建立 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)。

範例：在觸發程序中使用插入的資料表來實施業務規則

由於 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
        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 資料表中的每一資料行，都會直接對應到基底資料表中的資料行。

下列規則是關於當參考有 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 觸發程序的已插入或已刪除資料表，包含一個名為 BusinessEntityID的 int 欄位、一個名為 LName的 nvarchar 欄位，以及一個名為 FName的 nvarchar 欄位。

檢視的選取清單也可以包含不直接對應到單一基礎表格欄位的運算式。有些運算式，例如常數或函數調用，可能不參考任何資料行且可以被忽略。複雜運算式可以參考多個資料行，但 inserted 及 deleted 資料表的每個插入資料列只能有一個值。檢視中的簡單運算式若參考到具有複雜運算式的計算資料行，也會遇到相同的問題。檢視的觸發程序 (INSTEAD OF) 必須處理這些類型的運算式。

效能考量

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

下一步

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

共用方式為

使用「插入」和「刪除」資料表

了解已插入與已刪除的資料表

範例：在觸發程序中使用插入的資料表來實施業務規則

在 INSTEAD OF 觸發程序中使用 inserted 與 deleted 資料表

效能考量

下一步

意見反應

其他資源