在開發(fā)許多應(yīng)用程序時(shí)，有必要記錄某些數(shù)據(jù)表的歷史記錄或修改記錄，以便將來發(fā)生數(shù)據(jù)錯(cuò)誤時(shí)可以檢查數(shù)據(jù)。針對(duì)此業(yè)務(wù)需求，我們可以通過數(shù)據(jù)庫觸發(fā)器輕松實(shí)現(xiàn)歷史記錄功能。那么，SQL Server如何利用觸發(fā)器實(shí)現(xiàn)表的歷史修改痕跡的？本文以SQL Server 2005數(shù)據(jù)庫中的觸發(fā)器為例（因?yàn)槭种械捻?xiàng)目用的就是這個(gè)數(shù)據(jù)庫）。

　　先簡(jiǎn)單描述一下SQL Server觸發(fā)器

　　SQL Server觸發(fā)器的inserted和deleted

SQL Server為每個(gè)觸發(fā)器都創(chuàng)建了兩個(gè)專用虛擬表：inserted表和deleted表。這兩個(gè)表由系統(tǒng)來維護(hù)，他們存在于內(nèi)存中，而不是在數(shù)據(jù)庫中。這兩個(gè)表的結(jié)構(gòu)總是與被該觸發(fā)器作用的表結(jié)構(gòu)相同。觸發(fā)器執(zhí)行完成后，與該觸發(fā)器相關(guān)的兩個(gè)表會(huì)被刪除（即在內(nèi)存中銷毀）。

inserted表存放由執(zhí)行insert或update語句而要想飆中插入的所有行；即：插入后或更新后的值。

deleted表存放由delete或update語句而要從表中刪除的所有行；即：刪除或更新錢的值。

　　SQL Server觸發(fā)器的instead of和after

SQL Server提供了兩種觸發(fā)器：instead of和after觸發(fā)器。這兩種觸發(fā)器的區(qū)別在于他們被激活的時(shí)機(jī)不同：

· instead of觸發(fā)器用于替代引用觸發(fā)器執(zhí)行的sql語句。除表之外，instead of觸發(fā)器也可以用于視圖,用來擴(kuò)展視圖可以支持更新操作。

· after觸發(fā)器在一個(gè)inserted、update或delete語句之后執(zhí)行，進(jìn)行約束檢查等動(dòng)作都在after觸發(fā)器被激活之前發(fā)生。after觸發(fā)器只能用于數(shù)據(jù)表中。

說（復(fù)制）了這么多，是因?yàn)槲覀円獙?shí)現(xiàn)的功能需要用到inserted虛擬表、deleted虛擬表和after觸發(fā)器。

　　實(shí)現(xiàn)方法

通過一個(gè)示例來演示具體的實(shí)現(xiàn)方法。

假設(shè)當(dāng)前有一個(gè)表：產(chǎn)品表(product)，字段為“產(chǎn)品名(name)”、“產(chǎn)品描述(description)”、“單價(jià)(unit_cost)”和“生成日期(pub_time)”。

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

GO

現(xiàn)在我們”上帝”的需求是：需要記錄product表發(fā)生數(shù)據(jù)變化（增、刪、改）時(shí)，記錄每次操作改動(dòng)情況。

　　1.創(chuàng)建日志表

需要?jiǎng)?chuàng)建一個(gè)產(chǎn)品日志表(product_log)用來將記錄每次數(shù)據(jù)改動(dòng)情況，我這里直接在原數(shù)據(jù)表的結(jié)構(gòu)上增加兩個(gè)字段(在實(shí)際開發(fā)環(huán)境中，大家可以根據(jù)需求來設(shè)置日志表的表結(jié)構(gòu))，分別為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

新增的兩個(gè)字段sqlcomm和exectime分別記錄執(zhí)行命令（insert、update和delete）和執(zhí)行時(shí)間。

　　2.增加觸發(fā)器

在產(chǎn)品表增加觸發(fā)器，其目的是為了記錄表數(shù)據(jù)發(fā)生改變時(shí)記錄到product_log中。

針對(duì)插入（insert）操作，增加名為tr_product_i的觸發(fā)器：

1.CREATE TRIGGER tr_product_i

2.ON product

3.AFTER INSERT

4.AS

5.if @@rowcount = 0 --為了避免占用資源，當(dāng)影響行數(shù)為0時(shí)，結(jié)束觸發(fā)器

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

　　針對(duì)更新（update）操作，增加名為tr_product_u的觸發(fā)器：

1.CREATE TRIGGER tr_product_u

2.ON product

3.AFTER UPDATE

4.AS

5.if @@rowcount = 0 --為了避免占用資源，當(dāng)影響行數(shù)為0時(shí)，結(jié)束觸發(fā)器

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

　　針對(duì)刪除（delete）操作，增加名為tr_product_d的觸發(fā)器：

1.CREATE TRIGGER tr_product_d

2.ON product

3.AFTER DELETE

4.AS

5.if @@rowcount = 0 --為了避免占用資源，當(dāng)影響行數(shù)為0時(shí)，結(jié)束觸發(fā)器

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.測(cè)試觸發(fā)器

　　插入（insert）測(cè)試

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

2.VALUES('逗比','這是一個(gè)逗比的測(cè)試數(shù)據(jù)',200.5,'1990-11-18')

3.GO

4.SELECT * FROM product

5.SELECT * FROM product_log

6.GO

　　更新（update）測(cè)試

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

2.GO

3.SELECT * FROM product

4.SELECT * FROM product_log

5.GO

　　刪除（delete）測(cè)試

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

2.GO

3.SELECT * FROM product

4.SELECT * FROM product_log

5.GO

以上即是關(guān)于SQL Server如何利用觸發(fā)器實(shí)現(xiàn)表的歷史修改痕跡的全部?jī)?nèi)容，想了解更多關(guān)于SQL Server數(shù)據(jù)庫的信息，請(qǐng)繼續(xù)關(guān)注中培偉業(yè)。