SQL Server 用觸發器實現錶的歷史記錄

/Files/bgming/SqlHistory.rar 

 在許多應用中，需要儲存某個資料錶的歷史記錄，以供以後查詢，在 SQL Server 2000

中，可以利用觸發器輕鬆實現記錄功能。

 在觸發器中，需要用到 SQL Server 的 inserted 和 deleted 兩個虛擬表，在執行 sql
 
 命令時，這兩個虛擬表分別記錄的內容如下：
 
  sql命令       deleted               inserted
 ---------------------------------------------------
  insert        [不可用]             新插入的記錄
  update     被更新前的記錄         被更新後的記錄
  delete      被刪除的記錄             [不可用]
 ---------------------------------------------------

 下面通過一個例子來示範具體的實現方法。

 例如：
 
 當前有一個資料表 table1, 有 field1, field2, field3 三個欄位。
 
 現在我們需要在 table1 發生變化（插入，修改，刪除）時，記錄下每次改動的情況。
 
 這隻需要通過如下兩個步驟就可以實現：
 
 
 1） 建立一個新表 table1_log, 用於記錄 table1 的曆史資料。
 
  table1_log 有如下欄位：
  
  field1, field2, field3, sqlcomm (varchar 10), exectime (datetime)
 
  這比 table1 多了 sqlcomm, exectime 兩個欄位，分別記錄 sql 語句的 insert,
  
  update, delete 命令和命令執行的時間）
 
 
  
 2） 在表 table1 上增加觸發器，以在 table1 發生變動時，自動寫入 table1_log.
 
 
  -- 針對 insert 命令，增加名為 trTable1_I 的觸發器：
  
  -------------------------------- trTable1_I ----------------------------
  IF EXISTS (SELECT * FROM dbo.sysobjects
  WHERE id = object_id(N'[trTable1_I]')
  AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
  DROP TRIGGER [trTable1_I]
  GO
  
  CREATE TRIGGER trTable1_I
  ON table1
  AFTER insert
  AS

  if @@rowcount = 0 --如果影響的行數為 0，則結束觸發器運行，避免佔用資源
      return
   
  insert into table1_log (field1, field2, field3, sqlcomm, exectime)
  select field1, field2, field3, 'insert', {fn NOW()} from inserted
  
  go
  -------------------------------- end trTable1_I -----------------------
  
  
  -- 針對 update 命令，增加名為 trTable1_U 的觸發器：
  
  -------------------------------- trTable1_U ----------------------------
  IF EXISTS (SELECT * FROM dbo.sysobjects
  WHERE id = object_id(N'[trTable1_U]')
  AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
  DROP TRIGGER [trTable1_U]
  GO
  
  CREATE TRIGGER trTable1_U
  ON table1
  AFTER update
  AS

  if @@rowcount = 0 --如果影響的行數為 0，則結束觸發器運行，避免佔用資源
      return
   
  insert into table1_log (field1, field2, field3, sqlcomm, exectime)
  select field1, field2, field3, 'update', {fn NOW()} from inserted
  
  go
  -------------------------------- end trTable1_U -----------------------
  
  
  -- 針對 delete 命令，增加名為 trTable1_D 的觸發器：
  
  -------------------------------- trTable1_D ----------------------------
  IF EXISTS (SELECT * FROM dbo.sysobjects
  WHERE id = object_id(N'[trTable1_D]')
  AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
  DROP TRIGGER [trTable1_D]
  GO
  
  CREATE TRIGGER trTable1_D
  ON table1
  AFTER delete
  AS

  if @@rowcount = 0 --如果影響的行數為 0，則結束觸發器運行，避免佔用資源
      return
   
  insert into table1_log (field1, field2, field3, sqlcomm, exectime)
  select field1, field2, field3, 'delete', {fn NOW()} from deleted
  
  go
  -------------------------------- end trTable1_D -----------------------
  
  
  
 在查詢分析器中運行以上代碼，以後 table1 發生的變化都會記錄在 table1_log 表中了。
 
 （本人剛開始接觸“觸發器”，錯誤之處，敬請指正！）