触发器实例-trigger

SQL Server 用触发器实现表的历史记录
在许多应用中，需要保存某个数据表的历史记录，以供以后查询，在 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 表中了。