sqlserver触发器修改当前字段(利用SQL Server触发器实现表的历史修改痕迹记录)
sqlserver触发器修改当前字段
利用SQL Server触发器实现表的历史修改痕迹记录在很多应用程序开发中,需要记录某些数据表的历史记录或修改痕迹,以便日后出现数据错误时进行数据排查。这种业务需求,我们可以通过数据库的触发器来轻松实现历史记录功能。
本文以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;代码如下:
CREATE TABLE product_log(name VARCHAR(50),description VARCHAR(200),unit_cost MONEY,pub_time DATETIME,sqlcomm varchar(10),exectime datetime) GO
新增的两个字段sqlcomm和exectime分别记录执行命令(insert、update和delete)和执行时间
2.增加触发器
在产品表增加触发器,其目的是为了记录表数据发生改变时记录到product_log中。
针对插入(insert)操作,增加名为tr_product_i的触发器:
CREATE TRIGGER tr_product_i ON product AFTER INSERT AS if @@rowcount = 0 --为了避免占用资源,当影响行数为0时,结束触发器 return insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime) select name,description,unit_cost,pub_time,'insert',getdate() from inserted GO
针对更新(update)操作,增加名为tr_product_u的触发器:
CREATE TRIGGER tr_product_u ON product AFTER UPDATE AS if @@rowcount = 0 --为了避免占用资源,当影响行数为0时,结束触发器 return /*更新前*/ insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime) select name,description,unit_cost,pub_time,'update',getdate() from deleted /*更新后*/ insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime) select name,description,unit_cost,pub_time,'update',getdate() from inserted GO
针对删除(delete)操作,增加名为tr_product_d的触发器:
CREATE TRIGGER tr_product_d ON product AFTER DELETE AS if @@rowcount = 0 --为了避免占用资源,当影响行数为0时,结束触发器 return insert into product_log (name,description,unit_cost,pub_time,sqlcomm,exectime) select name,description,unit_cost,pub_time,'delete',getdate() from deleted GO
3.测试触发器
插入(insert)测试
INSERT INTO product(name,description,unit_cost,pub_time) VALUES('逗比','这是一个逗比的测试数据',200.5,'1990-11-18') GO SELECT * FROM product SELECT * FROM product_log GO
更新(update)测试
UPDATE product SET unit_cost=250.0 WHERE name='逗比' GO SELECT * FROM product SELECT * FROM product_log GO
删除(delete)测试
DELETE FROM product WHERE name='逗比' GO SELECT * FROM product SELECT * FROM product_log GO
好了这篇文章就介绍到这了,需要的朋友可以参考一下。
- sql server新建用户无法登录(sqlserver 因为选定的用户拥有对象,所以无法除去该用户的解决方法)
- mysql事务级别设置(mysql在项目中怎么选事务隔离级别)
- sql server2008脚本执行(在Sql Server中调用外部EXE执行程序引发的问题)
- mysql修改复杂密码设置(MySQL修改密码的几种方式)
- mysql账户访问权限(MySQL 权限控制详解)
- SQL SERVER中查看某个时间段内出错的作业
- mysql索引应该注意的地方(关于MySQL索引知识的小妙招)
- mysql 5.5.27 winx64安装配置方法图文教程(mysql 5.5.27 winx64安装配置方法图文教程)
- sqlserver 空间数据类型(SQL Server数据类型转换方法)
- mysql写入效率越来越差(MYSQL大量写入问题优化详解)
- 循环查询sql server(SQL Server 树形表非循环递归查询的实例详解)
- phpstudy怎么修改mysql版本(PhpStudy集成环境升级MySQL数据库版本的方法)
- 阿里云sql server 2012(远程连接阿里云SqlServer 2012 数据库服务器的图文教程)
- 对mysql索引的理解(详解MySQL 8.0 之不可见索引)
- mysqldump备份缺点(MySQL5.7 mysqldump备份与恢复的实现)
- mysql数据类型图解(MySQL数据库主从技术GTID大揭秘)
- 你或许不知道你隐藏的支配欲望(你或许不知道你隐藏的支配欲望)
- 把宽体丰田86卖了,换成7.5代高尔夫GTI玩起姿态与性能并存的改装(把宽体丰田86卖了)
- 大众推出了第五代高尔夫GT(大众推出了第五代高尔夫GT)
- 换代在即,现在是抄底 7.5代 高尔夫的最佳时机吗(换代在即现在是抄底)
- 2020年大众7.5代高尔夫R终结特别版 最后的呐喊(2020年大众7.5代高尔夫R终结特别版)
- 七年前的这部剧有毒,全剧只有女主红到发紫,男主至今无人认识(七年前的这部剧有毒)
热门推荐
- Linux 下如何检查内存使用率(Linux 下如何检查内存使用率)
- iis服务器安装错误(IIS7.0配置过程中出现HTTP 错误 403.14的解决方法)
- MVC中的ActionResult
- vue项目做过哪些打包优化(Vue项目优化的一些实战策略)
- linux目录操作功能(Linux 目录结构详细介绍)
- linq distinct去重
- 微信小程序js 抽奖概率(小程序实现筛子抽奖)
- dubbo和项目搭建(使用docker部署dubbo项目的方法步骤)
- ubuntu安装hadoop单机版(VMware + Ubuntu18.04 搭建Hadoop集群环境的图文教程)
- nginx 处理服务器错误(nginx服务器异常502 bad gateway原因排查)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9