系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > MsSql > 详细页面

利用SQL Server触发器实现表的历史修改痕迹记录

时间:2020-02-02来源:系统城作者:电脑系统城

在很多应用程序开发中,需要记录某些数据表的历史记录或修改痕迹,以便日后出现数据错误时进行数据排查。这种业务需求,我们可以通过数据库的触发器来轻松实现历史记录功能。

本文以SQL Server 2005数据库中的触发器为例(因为手中的项目用的就是这个数据库)

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

SQL Server触发器的inserted和deleted

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

inserted表存放由执行insert或update语句而要想飙中插入的所有行;即:插入后或更新后的值。
deleted表存放由delete或update语句而要从表中删除的所有行;即:删除或更新钱的值。

 

SQL操作 inserted表 deleted表
增加(insert)记录 存放新增的记录 [不可用]
修改(update)记录 存放更新后的记录 存放更新前的记录
删除(delete)记录 [不可用] 存放被删除的记录

 

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.  
  5. SELECT * FROM product
  6. SELECT * FROM product_log
  7. GO

更新(update)测试


 
  1. UPDATE product SET unit_cost=250.0 WHERE name='逗比'
  2. GO
  3.  
  4. SELECT * FROM product
  5. SELECT * FROM product_log
  6. GO

删除(delete)测试


 
  1. DELETE FROM product WHERE name='逗比'
  2. GO
  3.  
  4. SELECT * FROM product
  5. SELECT * FROM product_log
  6. GO

好了这篇文章就介绍到这了,需要的朋友可以参考一下。

分享到:

相关信息

  • SQL Server 数据库中的收缩数据库和文件操作

    收缩数据文件通过将数据页从文件末尾移动到更靠近文件开头的未占用的空间来恢复空间,在文件末尾创建足够的空间后,可取消对文件末尾的数据页的分配并将它们返回给文件系统,本文给大家介绍SQL Server 数据库中的收缩数据...

    2023-10-27

  • dbeaver配置SQL server连接实现

    一、需要java jdk环境,我用的比较新 二、dbeaver新建连接SQL server...

    2023-10-27

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载