在開發許多應用程序時，有必要記錄某些數據表的歷史記錄或修改記錄，以便將來發生數據錯誤時可以檢查數據。針對此業務需求，我們可以通過數據庫觸發器輕松實現歷史記錄功能。那么，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數據庫的信息，請繼續關注中培偉業。