触发器中两个临时表详解

触发器作为一种特殊的存储过程，它基于一个表而创建，与表紧密相连，可以看作是表的一部分，主要用来保证数据的完整性。在SQL Server中一张表可以有多个触发器，用户可以针对delete、insert和update语句分别设置触发器，当用户进行delete、insert和update等数据维护操作执行后，这些事先定义好的触发器对象就会被“触发”，并按事先定义好的规则自动执行。在触发器的“触发”过程中，有两个临时表inserted和deleted发生了作用。这两个特殊的临时表inserted和deleted，仅仅在触发器运行时存在，它们在某一特定时间和某一特定表相关。一旦某一个触发器结束执行时，相应的在二个表内的数据都会丢失。我们可以可以使用这两个表精确地确定触发器的动作对数据表所做的修改。这二个表在结构上与触发表结构相同，它们可用于触发器的条件测试，但用户不能直接更改它们的内容。此外，在delete、insert和update触发器中，禁止访问inserted和deleted表中的text、ntext和image数据类型列。因此，应先深入了解inserted和deleted表的结构和内容，才能更好地了解inserted和deleted表所发挥的作用。

我们以一个“教学”数据库为例，说明inserted和deleted表的结构、内容和所发挥的作用。假设该教学库有三个基本表，分别为“学生”、“课程”和“选课”。

学生（学生号 char(7)，姓名char(8)，性别char(2)，专业char(10)）

课程（课程号char(4)，课程名 char(20)，课程学分 int）

选课（学生号 char(7)，课程号char(4)，成绩 int）

　　首先我们考察在insert操作后，inserted和deleted表的结构、内容。为此我们建立一个针对insert操作的触发器，以反映inserted和deleted表的情况。

Create trigger in1 on 学生 for insert

As

 select * from inserted

select * from deleted

return

在执行了insert into 学生（学生号，姓名，性别，专业）values （‘0600102’，‘张三’，‘男’，‘电子’）后，运行效果见下图。

 

 

可见insert操作后，Inserted表内包含了所有已插入到表内的新记录，它记录了insert对触发器所在的表的改动。而deleted表中的内容为空。

　　其次我们考察在delete操作后，inserted和deleted表的结构、内容。为此我们建立一个针对delete操作的触发器，以反映inserted和deleted表的情况。

Create trigger de1 on 学生 for delete

as

  select * from inserted

select * from deleted

return

在执行了delete from学生 where姓名=‘张三’后，运行效果见下图。

 

 

可见delete操作后，deleted表内包含了所有被删除的记录，它记录了delete对触发器所在的表的改动。而inserted表中的内容为空。

　　最后我们考察在update操作后，inserted和deleted表的结构、内容。为此我们建立一个针对update操作的触发器，以反映inserted和deleted表的情况。

Create trigger up1 on 学生 for update

as

  select * from inserted

select * from deleted

return

在执行了update学生 set  性别=’女 where姓名=‘王明’后，运行效果见下图。

 

     可见update操作后，Inserted表内包含了update操作所涉及的所有记录修改后的值，deleted表内包含了update操作所涉及的所有记录修改前的值。它们记录了update对触发器所在的表的改动。

因此，在执行insert或update语句时，插入到触发表中的新行同时添加到inserted表中。而执行delete和update语句时，从触发表中删除的行被插入到deleted表中。执行update操作时，它先从表中删除旧行，然后再插入新行，其中被删除的行插入到deleted表中，而插入的新行则被同时记录到inserted临时表中。现将insert、delete和update三种情况下的Inserted和deleted表的内容总结归纳如下。

 

Inserted和deleted表的内容

临时表	Insert操作	Delete操作	Update操作
Inserted	所插入的内容	空	修改后的内容
deleted	空	所删除的内容	修改前的内容