MSSQL记录表字段数据变化的相关SQl
在软件实施过程中,也许会有这样的问题: 表中数据出现非预期的结果,此时不确定是程序问题,哪个程序,存储过程,触发器.. 或还是人为修改的结果,此时可以用触发器对特定的表字段做跟踪监视,记录每次新增,修改,删除此字段值的操作详细信息(含登录名,主机名,IP地址,执行的TSQL语句,程序名等等), 以利于问题的排查.
— 建测试表
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)
);
go
— 建跟踪触发器
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