SQL之恋(8)：触发器，SPROCS “变种Ⅱ”

软件世界

陈纯 · 2004年3月1日

　　触发器和存储过程一样，由一组T-SQL语句组成，区别在于：存储过程由用户调用执行，而触发器则由数据库系统自动调用执行，用户不能直接调用触发器程序；存储过程是独立的数据库对象，而触发器则依附于表或视图存在，是非独立的数据库对象。

　　SQL Server 2000支持两种触发器：传统的AFTER触发器和新引入的INSTEAD OF触发器。这两种触发器有很大差异。

　　感受AFTER触发器

　　首先我们一起看看下面的代码，注意代码中的注释。

　　--PART 1：在NorthWind数据库中创建实验用表Products_TEST

　　USE NorthWind

　　CREATE TABLE Products_TEST(ProductID int not null,ProductName nvarchar(80) not null)

　　-- PART 2：在表Products_TEST上创建AFTER INSERT 触发器

　　CREATE TRIGGER TrIns_Products_TEST ON Products_TEST

　　AFTER INSERT

　　DECLARE @Msg varchar(50)

　　SELECT @Msg='这次一共插入了 '+Cast(@@ROWCOUNT as varchar)+' 行数据'

　　PRINT @Msg

　　-- PART 3：测试触发器

　　INSERT INTO Products_TEST(ProductID，ProductName) SELECT ProductID，ProductName FROM Products WHERE CategoryID=1

　　--在查询分析器中显示：这次一共插入了 12 行数据

　　代码分析见下表。

　　AFTER触发器的特性

　　●AFTER触发器是在SQL Server 完成“触发操作”以后自动执行的。具体地讲，SQL Server发现用户对某表执行了INSERT操作，在该语句执行成功以后(数据被增加到表中)，SQL Server就自动执行该表上存在的AFTER INSERT触发器。

　　●有三类触发操作：INSERT、UPDATE和DELETE，分别由INSERT、UPDATE和DELETE语句激发。那么就有三种类型的AFTER触发器：AFTER INSERT、AFTER UPDATE和AFTER DELETE。

　　●AFTER触发器只能绑定在“表”上，而不能绑定在“视图”上。

　　深入AFTER触发器

　　AFTER触发器一般有两个用途：维护数据库的完整性和一致性。下面的例子涉及了触发器维护数据库一致性的功能。

　　USE NorthWind

　　--创建 Log_TEST 表，用于存放对Products_TEST的修改记录

　　CREATE TABLE Log_TEST(LogID int Identity(1,1) not null,LogDate DateTime,UserName varchar(20),Notes varchar(100))　　

　　--在 Products_TEST 表上创建 AFTER UPDATE 触发器

　　CREATE TRIGGER TrUpt_Products_TEST ON Products_TEST

　　AFTER UPDATE

　　IF @@ROWCOUNT=0

　　RETURN

　　IF Update(ProductName)

　　BEGIN

　　--把变动写入日志表 Log_TEST

　　INSERT INTO Log_TEST(LogDate，UserName，Notes) SELECT GetDate()，SYSTEM_USER，'产品名称从 '+a.ProductName+' 变为'+b.ProductName

　　FROM DELETED a INNER JOIN INSERTED b ON a.ProductID=b.ProductID

　　--同步更新 Products 表中的 ProductName 字段，保证数据的一致性

　　UPDATE Products SET ProductName=b.ProductName FROM Products a INNER JOIN INSERTED b ON a.ProductID=b.ProductID

　　END

　　UPDATE Products_TEST SET ProductName=ProductName+'测试' WHERE ProductID>70

　　上面的例子中我们揭示了触发器的内幕：INSERTED 和DELETED虚拟表。当用INSERT 语句插入数据时，INSERTED虚拟表保存了新插入的数据集；当用DELETE语句删除数据时，DELETED虚拟表保存了被删除的数据集；当用UPDATE语句修改数据时(读者可以把UPDATE理解成先DELETE再INSERT的操作)，DELETED虚拟表中保存了被更改前的数据集，INSERTED虚拟表中保存了更改后的数据集。

　　上述代码的分析见右表。

　　INSTEAD OF触发器简介

　　前面笔者提到，很难通过操作视图更新基表。例如我们建立了分区视图(用UNION连接)，然后执行INSERT语句往视图中插入数据，SQL Server 会弹出“派生表 'XXX' 不可更新，因为其定义中包含 UNION 运算符”的出错提示，这是因为SQL Server 不知道应该把数据写入哪张基表中。不过，另一类触发器──INSTEAD OF触发器，就可以解决这个问题。INSTEAD OF触发器的运行原理与AFTER 触发器大相径庭：

　　●INSTEAD OF触发器可以绑定在表和视图上(一般在视图上)，AFTER触发器只能绑定在表上。

　　●和AFTER触发器一样，INSTEAD OF触发器由INSERT、UPDATE和DELETE三个操作触发；一个表可以绑定多个AFTER INSERT、AFTER UPDATE、AFTER DELETE触发器，而在视图(或表)上每种类型的INSTEAD OF触发器只能有一个。

　　●当在视图(或表)上执行INSERT(或UPDATE、DELETE)时，如果SQL Server 发现在视图(或表)上有相应的INSTEAD OF触发器，SQL Server就会取消对视图(或表)的实际操作，转为执行INSTEAD OF触发器。

　　在前面三期中，我们初步了解了存储过程、自定义函数和触发器，笔者故意避开了涉及事务的内容，下期朋友们将了解到支持事务处理的存储过程和触发器。