使用插入的和删除的表

项目
06/13/2017

DML 触发器语句使用两种特殊的表：删除的表和插入的表。 SQL Server 会自动创建和管理这两种表。您可以使用这两种驻留内存的临时表来测试特定数据修改的影响以及设置 DML 触发器操作条件。但不能直接修改表中的数据或对表执行数据定义语言 (DDL) 操作，例如 CREATE INDEX。

在 DML 触发器中，inserted 和 deleted 表主要用于执行以下操作：

扩展表之间的引用完整性。
在以视图为基础的基表中插入或更新数据。
检查错误并采取相应的措施。
找出数据修改前后表的状态差异并基于该差异采取相应的措施。

删除的表用于存储 DELETE 和 UPDATE 语句所影响的行的副本。在执行 DELETE 或 UPDATE 语句的过程中，行从触发器表中删除，并传输到删除的表中。删除的表和触发器表通常没有相同的行。

插入的表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在执行插入或更新事务过程中，新行会同时添加到 inserted 表和触发器表中。插入的表中的行是触发器表中的新行的副本。

更新事务类似于在删除操作之后执行插入操作；首先，旧行被复制到删除的表中，然后，新行被复制到触发器表和插入的表中。

在设置触发器条件时，应使用激发触发器的操作相应的插入的和删除的表。尽管在测试 INSERT 时引用删除的表或在测试 DELETE 时引用插入的表不会导致任何错误，但在这些情况下，这些触发器测试表将不包含任何行。

注意

如果触发器操作取决于一个数据修改所影响的行数，则应对多行数据修改（基于 SELECT 语句的 INSERT、DELETE 或 UPDATE）使用测试（例如检查 @@ROWCOUNT），然后采取相应的措施。

SQL Server 2014 不允许text在插入和删除的 AFTER 触发器的表中引用、 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 表并将其与插入的表联接。如果信用等级太低，则显示信息，并且不执行该插入操作。请注意，此示例不允许进行多行数据修改。有关详细信息，请参阅创建 DML 触发器以处理多行数据。

USE AdventureWorks2012;
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 EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.BusinessEntityID = p.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 触发器中使用插入的和删除的表

传递给为表定义的 INSTEAD OF 触发器的插入的和删除的表与传递给 AFTER 触发器的插入的和删除的表遵守相同的规则。插入的和删除的表的格式与在其上定义 INSTEAD OF 触发器的表的格式相同。插入的和删除的表中的每一列都直接映射到基表中的列。

以下是关于引用带 INSTEAD OF 触发器的表的 INSERT 或 UPDATE 语句何时必须提供列值的规则，当引用的表不带 INSTEAD OF 触发器时也一样：

不能为计算列或具有 timestamp 数据类型的列指定值。
不能为具有 IDENTITY 属性的列指定值，除非该表的 IDENTITY_INSERT 为 ON。当 IDENTITY_INSERT 为 ON 时，INSERT 语句必须提供一个值。
INSERT 语句必须为所有无 DEFAULT 约束的 NOT NULL 列提供值。
对于除计算列、标识列或 timestamp 列以外的任何列，任何允许空值的列或具有 DEFAULT 定义的 NOT NULL 列的值都是可选的。

当 INSERT、UPDATE 或 DELETE 语句引用具有 INSTEAD OF 触发器的视图时，数据库引擎将调用该触发器，而不是对任何表采取任何直接操作。即使插入的和删除的表中为该视图生成的信息格式不同于基表中的数据格式，触发器也必须使用插入的和删除的表中的信息来生成实现基表中请求的操作所需的任何语句。

传递给为视图定义的 INSTEAD OF 触发器的插入的和删除的表的格式与为该视图定义的 SELECT 语句的选择列表的格式一致。例如：

USE AdventureWorks2012;  
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 列。

视图的选择列表还可以包含不直接映射到单个基表列的表达式。一些视图表达式（例如常量调用或函数调用）可能不引用任何列，并且这些表达式会被忽略。复杂的表达式会引用多个列，但在插入的和删除的表中，每个插入的行仅有一个相应的值。如果视图中的简单表达式引用包含复杂表达式的计算列，则这些简单表达式也有同样的问题。视图上的 INSTEAD OF 触发器必须处理这些类型的表达式。

通过

使用插入的和删除的表

在 INSTEAD OF 触发器中使用插入的和删除的表

其他资源