在开发许多应用程序时，有必要记录某些数据表的历史记录或修改记录，以便将来发生数据错误时可以检查数据。针对此业务需求，我们可以通过数据库触发器轻松实现历史记录功能。那么，SQL Server如何利用触发器实现表的历史修改痕迹的？本文以SQL Server 2005数据库中的触发器为例（因为手中的项目用的就是这个数据库）。

　　先简单描述一下SQL Server触发器

　　SQL Server触发器的inserted和deleted

SQL Server为每个触发器都创建了两个专用虚拟表：inserted表和deleted表。这两个表由系统来维护，他们存在于内存中，而不是在数据库中。这两个表的结构总是与被该触发器作用的表结构相同。触发器执行完成后，与该触发器相关的两个表会被删除（即在内存中销毁）。

inserted表存放由执行insert或update语句而要想飙中插入的所有行；即：插入后或更新后的值。

deleted表存放由delete或update语句而要从表中删除的所有行；即：删除或更新钱的值。

　　SQL Server触发器的instead of和after

SQL Server提供了两种触发器：instead of和after触发器。这两种触发器的区别在于他们被激活的时机不同：

· instead of触发器用于替代引用触发器执行的sql语句。除表之外，instead of触发器也可以用于视图,用来扩展视图可以支持更新操作。

· after触发器在一个inserted、update或delete语句之后执行，进行约束检查等动作都在after触发器被激活之前发生。after触发器只能用于数据表中。

说（复制）了这么多，是因为我们要实现的功能需要用到inserted虚拟表、deleted虚拟表和after触发器。

　　实现方法

通过一个示例来演示具体的实现方法。

假设当前有一个表：产品表(product)，字段为“产品名(name)”、“产品描述(description)”、“单价(unit_cost)”和“生成日期(pub_time)”。

CREATE TABLE product(name VARCHAR(50),description VARCHAR(200),unit_cost MONEY,pub_time DATETIME)

GO

现在我们”上帝”的需求是：需要记录product表发生数据变化（增、删、改）时，记录每次操作改动情况。

　　1.创建日志表

需要创建一个产品日志表(product_log)用来将记录每次数据改动情况，我这里直接在原数据表的结构上增加两个字段(在实际开发环境中，大家可以根据需求来设置日志表的表结构)，分别为sqlcomm和exectime;代码如下：

1）.CREATE TABLE product_log(name VARCHAR(50),description VARCHAR(200),unit_cost MONEY,pub_time DATETIME,sqlcomm varchar(10),exectime datetime)

2）.GO

新增的两个字段sqlcomm和exectime分别记录执行命令（insert、update和delete）和执行时间。

　　2.增加触发器

在产品表增加触发器，其目的是为了记录表数据发生改变时记录到product_log中。

针对插入（insert）操作，增加名为tr_product_i的触发器：

1.CREATE TRIGGER tr_product_i

2.ON product

3.AFTER INSERT

4.AS

5.if @@rowcount = 0 --为了避免占用资源，当影响行数为0时，结束触发器

6.return

7.insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime)

8.select name,description,unit_cost,pub_time,'insert',getdate() from inserted

9.GO

　　针对更新（update）操作，增加名为tr_product_u的触发器：

1.CREATE TRIGGER tr_product_u

2.ON product

3.AFTER UPDATE

4.AS

5.if @@rowcount = 0 --为了避免占用资源，当影响行数为0时，结束触发器

6.return

7./*更新前*/

8.insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime)

9.select name,description,unit_cost,pub_time,'update',getdate() from deleted

10./*更新后*/

11.insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime)

12.select name,description,unit_cost,pub_time,'update',getdate() from inserted

13.GO

　　针对删除（delete）操作，增加名为tr_product_d的触发器：

1.CREATE TRIGGER tr_product_d

2.ON product

3.AFTER DELETE

4.AS

5.if @@rowcount = 0 --为了避免占用资源，当影响行数为0时，结束触发器

6.return

7.insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime)

8.select name,description,unit_cost,pub_time,'delete',getdate() from deleted

9.GO

　　3.测试触发器

　　插入（insert）测试

1.INSERT INTO product(name,description,unit_cost,pub_time)

2.VALUES('逗比','这是一个逗比的测试数据',200.5,'1990-11-18')

3.GO

4.SELECT * FROM product

5.SELECT * FROM product_log

6.GO

　　更新（update）测试

1.UPDATE product SET unit_cost=250.0 WHERE name='逗比'

2.GO

3.SELECT * FROM product

4.SELECT * FROM product_log

5.GO

　　删除（delete）测试

1.DELETE FROM product WHERE name='逗比'

2.GO

3.SELECT * FROM product

4.SELECT * FROM product_log

5.GO

以上即是关于SQL Server如何利用触发器实现表的历史修改痕迹的全部内容，想了解更多关于SQL Server数据库的信息，请继续关注中培教育。