触发器记录表某一个字段数据变化的日志 包括插入insert 修改update 删除delete 操作
2023-09-14 08:57:24 时间
本文参考:http://www.cnblogs.com/lyhabc/articles/3236985.html
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--触发器记录表某一个字段数据变化的日志 包括插入insert 修改update 删除delete 操作 2013-6-18
-- 建测试表
USE [pratice]
GO
create table sto
(id int not null, -- 主键字段
de datetime -- 被跟踪的字段
constraint pk_sto primary key(id)
)
-- 建日志表
create table log_sto
(logid int not null identity(1,1), -- 日志序号(日志主键)
operate varchar(10), -- 操作类型 如Insert,Update,Delete.
id int, -- 原表ID(主键)
old_de datetime, -- de字段旧值
new_de datetime, -- de字段新值
spid int not null, -- spid
login_name varchar(100), -- 登录名
prog_name varchar(100), -- 程序名
hostname varchar(100), -- 主机名
ipaddress varchar(100), -- IP地址
runsql varchar(4000), -- 执行的TSQL代码
UDate datetime -- 操作日期时间
constraint pk_logsto primary key(logid)
)
-- 建跟踪触发器
create trigger tr_sto
on sto after update,insert,delete
as
begin
declare @di table(et varchar(200),pt varchar(200),ei varchar(max))
insert into @di exec('dbcc inputbuffer(@@spid)')
declare @op varchar(10)
select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Update'
when exists(select 1 from inserted) and not exists(select 1 from deleted)
then 'Insert'
when not exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Delete' end
if @op in('Update','Insert')
begin
insert into log_sto
(operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
select @op,n.id,o.de,n.de,@@spid,
(select login_name from sys.dm_exec_sessions where session_id=@@spid),
(select program_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address from sys.dm_exec_connections where session_id=@@spid),
(select top 1 isnull(ei,'') from @di),
getdate()
from inserted n
left join deleted o on o.id=n.id
end
else
begin
insert into log_sto
(operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
select @op,o.id,o.de,null,@@spid,
(select login_name from sys.dm_exec_sessions where session_id=@@spid),
(select program_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address from sys.dm_exec_connections where session_id=@@spid),
(select top 1 isnull(ei,'') from @di),
getdate()
from deleted o
end
end
go
--> 测试DML操作
-- 操作1
insert into sto(id,de) values(1,'2012-01-01 05:06:07')
go
-- 操作2
insert into sto(id,de) values(2,'2012-01-01 06:06:07')
go
-- 操作3
update sto set de=getdate() where id=2
go
-- 操作4
update sto set de=getdate() where id=1
go
-- 操作5
insert into sto(id,de) values(3,'2012-01-01 15:26:37')
go
-- 操作6
delete sto where id=2
GO
SELECT * FROM log_sto
-- 建测试表
USE [pratice]
GO
create table sto
(id int not null, -- 主键字段
de datetime -- 被跟踪的字段
constraint pk_sto primary key(id)
)
-- 建日志表
create table log_sto
(logid int not null identity(1,1), -- 日志序号(日志主键)
operate varchar(10), -- 操作类型 如Insert,Update,Delete.
id int, -- 原表ID(主键)
old_de datetime, -- de字段旧值
new_de datetime, -- de字段新值
spid int not null, -- spid
login_name varchar(100), -- 登录名
prog_name varchar(100), -- 程序名
hostname varchar(100), -- 主机名
ipaddress varchar(100), -- IP地址
runsql varchar(4000), -- 执行的TSQL代码
UDate datetime -- 操作日期时间
constraint pk_logsto primary key(logid)
)
-- 建跟踪触发器
create trigger tr_sto
on sto after update,insert,delete
as
begin
declare @di table(et varchar(200),pt varchar(200),ei varchar(max))
insert into @di exec('dbcc inputbuffer(@@spid)')
declare @op varchar(10)
select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Update'
when exists(select 1 from inserted) and not exists(select 1 from deleted)
then 'Insert'
when not exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Delete' end
if @op in('Update','Insert')
begin
insert into log_sto
(operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
select @op,n.id,o.de,n.de,@@spid,
(select login_name from sys.dm_exec_sessions where session_id=@@spid),
(select program_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address from sys.dm_exec_connections where session_id=@@spid),
(select top 1 isnull(ei,'') from @di),
getdate()
from inserted n
left join deleted o on o.id=n.id
end
else
begin
insert into log_sto
(operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
select @op,o.id,o.de,null,@@spid,
(select login_name from sys.dm_exec_sessions where session_id=@@spid),
(select program_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address from sys.dm_exec_connections where session_id=@@spid),
(select top 1 isnull(ei,'') from @di),
getdate()
from deleted o
end
end
go
--> 测试DML操作
-- 操作1
insert into sto(id,de) values(1,'2012-01-01 05:06:07')
go
-- 操作2
insert into sto(id,de) values(2,'2012-01-01 06:06:07')
go
-- 操作3
update sto set de=getdate() where id=2
go
-- 操作4
update sto set de=getdate() where id=1
go
-- 操作5
insert into sto(id,de) values(3,'2012-01-01 15:26:37')
go
-- 操作6
delete sto where id=2
GO
SELECT * FROM log_sto
改进版本:1、只记录Update字段时,新老值不一致情况。注意NULL是不能进行比较。需要用到Isnull进行转换后,方可进行比较操作。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- 建跟踪触发器
ALTER trigger tr_sto
on sto after update,insert,delete
as
begin
declare @di table(et NVARCHAR(max),pt NVARCHAR(max),ei NVARCHAR(max))
insert into @di exec('dbcc inputbuffer(@@spid)')
declare @op varchar(10)
select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Update'
when exists(select 1 from inserted) and not exists(select 1 from deleted)
then 'Insert'
when not exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Delete' end
if @op ='Update'
--IF UPDATE(de) --关键字段发生表更记录。
--BEGIN
BEGIN
insert into log_sto
(operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
select @op,n.id,o.de,n.de,@@spid,
(select login_name from sys.dm_exec_sessions where session_id=@@spid),
(select program_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address from sys.dm_exec_connections where session_id=@@spid),
(select top 1 isnull(ei,'') from @di),
getdate()
from inserted n
left join deleted o on o.id=n.id
WHERE ISNULL(o.de,'')<>ISNULL(n.de,'') --只记录修改字段old值和new值不一样的情况。
--WHERE CHECKSUM(o.de)<>CHECKSUM(n.de)
END
--end
ELSE if @op ='Insert'
begin
insert into log_sto
(operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
select @op,n.id,o.de,n.de,@@spid,
(select login_name from sys.dm_exec_sessions where session_id=@@spid),
(select program_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address from sys.dm_exec_connections where session_id=@@spid),
(select top 1 isnull(ei,'') from @di),
getdate()
from inserted n
left join deleted o on o.id=n.id
end
else
begin
insert into log_sto
(operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
select @op,o.id,o.de,null,@@spid,
(select login_name from sys.dm_exec_sessions where session_id=@@spid),
(select program_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address from sys.dm_exec_connections where session_id=@@spid),
(select top 1 isnull(ei,'') from @di),
getdate()
from deleted o
end
end
go
ALTER trigger tr_sto
on sto after update,insert,delete
as
begin
declare @di table(et NVARCHAR(max),pt NVARCHAR(max),ei NVARCHAR(max))
insert into @di exec('dbcc inputbuffer(@@spid)')
declare @op varchar(10)
select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Update'
when exists(select 1 from inserted) and not exists(select 1 from deleted)
then 'Insert'
when not exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Delete' end
if @op ='Update'
--IF UPDATE(de) --关键字段发生表更记录。
--BEGIN
BEGIN
insert into log_sto
(operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
select @op,n.id,o.de,n.de,@@spid,
(select login_name from sys.dm_exec_sessions where session_id=@@spid),
(select program_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address from sys.dm_exec_connections where session_id=@@spid),
(select top 1 isnull(ei,'') from @di),
getdate()
from inserted n
left join deleted o on o.id=n.id
WHERE ISNULL(o.de,'')<>ISNULL(n.de,'') --只记录修改字段old值和new值不一样的情况。
--WHERE CHECKSUM(o.de)<>CHECKSUM(n.de)
END
--end
ELSE if @op ='Insert'
begin
insert into log_sto
(operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
select @op,n.id,o.de,n.de,@@spid,
(select login_name from sys.dm_exec_sessions where session_id=@@spid),
(select program_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address from sys.dm_exec_connections where session_id=@@spid),
(select top 1 isnull(ei,'') from @di),
getdate()
from inserted n
left join deleted o on o.id=n.id
end
else
begin
insert into log_sto
(operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
select @op,o.id,o.de,null,@@spid,
(select login_name from sys.dm_exec_sessions where session_id=@@spid),
(select program_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address from sys.dm_exec_connections where session_id=@@spid),
(select top 1 isnull(ei,'') from @di),
getdate()
from deleted o
end
end
go
相关文章
- C#轻量级高性能日志组件EasyLogger
- PHP读取日志里数据方法理解
- 大数据应用日志采集之Scribe演示实例完全解析
- PHP读取日志里数据方法理解
- 第四十五节,logging日志模块
- 解决websphere在aix linux下日志乱码
- 从零入门 Serverless | Serverless 应用如何管理日志 & 持久化数据
- 开启引导日志
- rsyslog传输指定目录下的全部日志数据
- postgresql 的三类日志
- rsyslog传输指定目录下的全部日志数据
- 接口服务中的日志
- 如何将 winston log 库记录的日志写入 mongo DB 数据库
- 用ELK打造可视化集中式日志
- 云数据库的数据和日志备份下载
- 【华为OD机试 2023最新 】 日志首次上报最多积分(C++ 100%)
- centos7搭建ELK开源实时日志分析系统
- Mysql 通过binlog日志恢复数据
- 大数据平台网站日志分析系统
- kubectl 获取pod日志 —— 筑梦之路
- 67:内网安全-域横向smb&wmi明文或hash传递——注意:Windows 默认不会将 WMI 的操作记录在日志里,隐蔽性极强,EDR数据采集的时候注意
- redis_04 _ AOF日志:宕机了,Redis如何避免数据丢失
- Grafana配置对接Loki搜索日志
- 使用Logstash过滤插件Grok直接写正则表达式获取日志数据
- Logstash数据处理服务将采集的不同日志数据存储到不同的ES索引库(四)