1 --触发器记录表某一个字段数据变化的日志 包括插入insert 修改update 删除delete 操作 2013-6-18
2 -- 建测试表
3 USE [pratice]
4 GO
5 create table sto
6 (id int not null, -- 主键字段
7 de datetime -- 被跟踪的字段
8 constraint pk_sto primary key(id)
9 )
10
11 -- 建日志表
12 create table log_sto
13 (logid int not null identity(1,1), -- 日志序号(日志主键)
14 operate varchar(10), -- 操作类型 如Insert,Update,Delete.
15 id int, -- 原表ID(主键)
16 old_de datetime, -- de字段旧值
17 new_de datetime, -- de字段新值
18 spid int not null, -- spid
19 login_name varchar(100), -- 登录名
20 prog_name varchar(100), -- 程序名
21 hostname varchar(100), -- 主机名
22 ipaddress varchar(100), -- IP地址
23 runsql varchar(4000), -- 执行的TSQL代码
24 UDate datetime -- 操作日期时间
25 constraint pk_logsto primary key(logid)
26 )
27
28
29 -- 建跟踪触发器
30 create trigger tr_sto
31 on sto after update,insert,delete
32 as
33 begin
34 declare @di table(et varchar(200),pt varchar(200),ei varchar(max))
35 insert into @di exec('dbcc inputbuffer(@@spid)')
36
37 declare @op varchar(10)
38 select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)
39 then 'Update'
40 when exists(select 1 from inserted) and not exists(select 1 from deleted)
41 then 'Insert'
42 when not exists(select 1 from inserted) and exists(select 1 from deleted)
43 then 'Delete' end
44
45 if @op in('Update','Insert')
46 begin
47 insert into log_sto
48 (operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
49 select @op,n.id,o.de,n.de,@@spid,
50 (select login_name from sys.dm_exec_sessions where session_id=@@spid),
51 (select program_name from sys.dm_exec_sessions where session_id=@@spid),
52 (select hostname from sys.sysprocesses where spid=@@spid),
53 (select client_net_address from sys.dm_exec_connections where session_id=@@spid),
54 (select top 1 isnull(ei,'') from @di),
55 getdate()
56 from inserted n
57 left join deleted o on o.id=n.id
58 end
59 else
60 begin
61 insert into log_sto
62 (operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
63 select @op,o.id,o.de,null,@@spid,
64 (select login_name from sys.dm_exec_sessions where session_id=@@spid),
65 (select program_name from sys.dm_exec_sessions where session_id=@@spid),
66 (select hostname from sys.sysprocesses where spid=@@spid),
67 (select client_net_address from sys.dm_exec_connections where session_id=@@spid),
68 (select top 1 isnull(ei,'') from @di),
69 getdate()
70 from deleted o
71 end
72 end
73 go
74
75
76 --> 测试DML操作
77
78 -- 操作1
79 insert into sto(id,de) values(1,'2012-01-01 05:06:07')
80 go
81
82 -- 操作2
83 insert into sto(id,de) values(2,'2012-01-01 06:06:07')
84 go
85
86 -- 操作3
87 update sto set de=getdate() where id=2
88 go
89
90 -- 操作4
91 update sto set de=getdate() where id=1
92 go
93
94 -- 操作5
95 insert into sto(id,de) values(3,'2012-01-01 15:26:37')
96 go
97
98 -- 操作6
99 delete sto where id=2
100 GO
101
102 SELECT * FROM log_sto
