在软件实施过程中,也许会有这样的问题: 表中数据出现非预期的结果,此时不确定是程序问题,哪个程序,存储过程,触发器.. 或还是人为修改的结果,此时可以用触发器对特定的表字段做跟踪监视,记录每次新增,修改,删除此字段值的操作详细信息(含登录名,主机名,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
(
5, ‘2012-01-01 15:26:37’
);
go
— 操作6
delete sto
where
id = 2;
go