本文参考:http://www.cnblogs.com/lyhabc/articles/3236985.html
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.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://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.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