利用SQL Server触发器实现表的历史修改痕迹记录
作者:mdxy-dxy 发布时间:2024-01-19 18:06:42
在很多应用程序开发中,需要记录某些数据表的历史记录或修改痕迹,以便日后出现数据错误时进行数据排查。这种业务需求,我们可以通过数据库的触发器来轻松实现历史记录功能。
本文以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
好了这篇文章就介绍到这了,需要的朋友可以参考一下。
来源:https://wuzhuti.cn/sql-server-trigger-data-history
猜你喜欢
- pygame创建游戏窗口界面,供大家参考,具体内容如下使用pygame前一定要先导入pygame而且肯定要先初始化pygameimport
- 问题在使用MySQL数据库的时候,经常会遇到这么一个问题,就是“Can not connect to MySQL server. Too m
- 用javascript实现Base64编码—解决中文问题因javascript求出来的是Unicode要转换成Ansi后才能对它进行Base
- 人们对于那些抄袭模仿的网站有诸多抱怨,但在这篇文章中,却没有冷嘲热讽的意思。但正如他们所说,“模仿是最为忠诚的奉承形式”。“如果你确实需要借
- 前言在写波段配准相关代码时经常需要用到tif影像的波段合成和分解,虽然可以用ENVI才处理,但是每次都要打开再设置一些参数有些麻烦,所以本着
- 做计算机视觉方向,除了流行的各种深度学习算法,很多时候也要会基础的图像处理方法。记录下opencv的一些操作(图像映射变换),日后可以方便使
- template中的_parse方法是模板文法的解析器,而这个文件中一坨一坨的各种node以及block,就是解析结果的承载者,也就是说在经
- 一、split()函数的简单应用1.join()函数Python join() 方法用于将序列中的元素以指定的字符连接生成一个新的字符串。j
- 背景:准备给长辈买个手机,有关手机大小,网购平台基本只有手机尺寸和分辨率的文本数据,因而对手机屏幕大小没有直观感受,虽然网上有比较手机大小的
- 分享一个 * 真网页拾色器(调色板),颜色丰富216色,使用方便。运行截图:<html id="container"
- 刚才运行了一段代码,来查看Request.ServerVariables里面有多少值,看了一下,共50个!代码<%=Request.S
- 看起来现在经常用到这样的效果来提高用户体验,所以就没事写了一个输入框提示列表的效果使用宽屏的朋友麻烦帮忙测试下,列表的位置有没有错位。代码可
- 虽然现在有许多网页制作工具能让您轻松地完成工作,但如果使用HTML则可以得到更大控制权,下面介绍几个小技巧。1.使用语句来控制文字排版比用好
- 1、同级目录下调用若在程序 testone.py 中导入模块 testtwo.py , 则直接使用【import testtwo 或 fro
- 一、导包import pandas as pdimport matplotlib.pyplot as plt二、绘制简单折线数据:有一个Ex
- 前提条件,percona 5.6版本,事务隔离级别为RRmysql> show create table test_autoinc_l
- 需求通过分析nginx访问日志,获取每个接口响应时间最大值、最小值、平均值及访问量。实现原理将nginx日志uriuriupstream_r
- 自己从工艺品设计到平面设计到网络设计,虽然设计原则不离其宗,但经验下来的心得告诉自己,设计媒介的变化带来很多媒介自身的特殊性,下面总结下网站
- 如何显示数据库的结构?<html><head><meta http-equiv="Cont
- 转自http://rookiefly.cn/detail/69作死小能手这两天闲着没事,把自己电脑重装了,然而重装过后配置开发环境踩了一些坑