SQL使用Inserted和Deleted表

我们这里介绍两个特殊的表，Inserted表和Deleted表。此二表仅仅在触发器运行时存在。你可以使用该两个表来精确地确定触发触发器的动作对数据表所做的修改。比如，通过检查Deleted表，你可以确定那些记录由某一动作删除。考虑下面的例子：

CREATE TRIGGER tr_webusers_delete ON webusers

FOR DELETE

AS

INSERT weblog (activity) SELECT user_name FROM Deleted

该触发器自动地创建一个webusers表的记录。当在webusers表内删除一个用户的姓名时，触发器会自动地把该姓名插入到weblog表中。假设你一不小心执行了下面的语句：

DELETE webusers

该语句会删除在webusers表内的所有记录。一般地，这些记录会永久地丢失，然而上面的触发器会在有记录从webusers表中删除时自动的触发。该触发器会检查表Deleted来确定有那些在webusers表内的记录被删除，并且把所有删除了的记录拷贝到weblog表中。

为了恢复那些意外被删除的记录，你可以使用INSERT和SELECT语句再一次把它们从weblog表中拷贝到webusers表中。如果你不能允许意外地丢失一条记录时，你可以使用上面的方法来创建一个表内数据的备份。

Deleted表和有记录被删除的表的列结构一模一样。在前面的例子内，Deleted表具有和webusers表相同的结构。

现在假定你想跟踪所有插入某一表格的记录。比如，你想把每一条插入webusers表内的记录都在weblog表内做备份，你可以使用下面的触发器来完成该任务：

CREATE TRIGGER tr_webusers_insert ON webusers

FOR INSERT

AS

INSERT weblog(activty) SELECT user_name FROM INSERTED

该触发器和前面的触发器非常相似，除了以下两点以外：

该触发器在有记录插入表格webusers时触发；该触发器是FOR INSERT。

该触发器把记录从Inserted表拷贝到weblog表内。

Inserted表内包含了所有已经插入到表内的新记录。假如一个新用户的姓名插入到webusers表内时，触发器会自动地把新的用户姓名从webusers表拷贝到weblog表内。

当你想使用一个简单的表来记录所有发生在你数据库中一个非常重要的表的动作时，这种复制数据的方法非常有用。你可以使用该表来获得对你的数据库的活动的记录，并且可以用于记录和诊断一些可能发生的问题。

你同样可以使用Inserted表和Deleted表来记录UPDATE对触发器所在的表所做的改动。当一个和触发器相关的表内的数据被修改时，Deleted表包含了所有列在修改之前的值，而Inserted表包含了所有列在修改之后的值。参看下面的表12.2，以明确每一个动作是如何影响Deleted和Inserted表的。

表12.2.Inserted和Deleted表的内容

表	INSERT	DELETE	UPDATE
Inserted	插入列	空	修改前的列
Deleted	空	删除列	修改后的列

 

 

 

 

 

 

 

 

 

 使用 inserted 和 deleted 表

 (2009-03-12 12:44:13)

转载

标签：  杂谈

分类： 工作所用

使用 inserted 和 deleted 表 updated
触发器语句中使用了两种特殊的表：deleted 表和 inserted 表。Microsoft® SQL Server™ 2000 自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件；然而，不能直接对表中的数据进行更改。

inserted 和 deleted 表主要用于触发器中：

扩展表间引用完整性。

在以视图为基础的基表中插入或更新数据。

检查错误并基于错误采取行动。

找到数据修改前后表状态的差异，并基于此差异采取行动。
Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。

Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。

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

在设置触发器条件时，应当为引发触发器的操作恰当使用 inserted 和 deleted 表。虽然在测试 INSERT 时引用 deleted 表或在测试 DELETE 时引用 inserted 表不会引起任何错误，但是在这种情形下这些触发器测试表中不会包含任何行。

 

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

1.插入操作（Insert）

Inserted表有数据，Deleted表无数据

2.删除操作（Delete）

Inserted表无数据，Deleted表有数据

3.更新操作（Update）

Inserted表有数据（新数据），Deleted表有数据（旧数据）
SQL Server™ 2000 不允许 AFTER 触发器引用 inserted 和 deleted 表中的 text、ntext 或 image 列；然而，允许 INSTEAD OF 触发器引用这些列。有关更多信息，请参见 CREATE TRIGGER。

 

Inserted为空,Deleted不为空   --Delete
Inserted不为空,Deleted为空   --Insert
Inserted不为空,Deleted不为空   --Update

 

alter TRIGGER update_oatable_state
ON oatable
FOR update
AS

declare @zid nvarchar(100)
declare @sub2 nvarchar(100)

select  @zid=zid, @sub2=sub2   from   updated
 update   T_P_OA_ZZWJGL   set   v5=@sub2 where zid = @zid

GO

如果你保证每次只更新一条记录： 
  
  CREATE   TRIGGER   UpdateUser   ON   dbo.[user]   
  after   update 
  AS 
  update   users   set   userid=new.UserName,[password]=new.UserPassword   from   inserted   new  where   users.userid=(select   username   from   deleted) 
  go