笔记301 触发器记录表某一个字段数据变化的日志 包括插入insert 修改update 删除delete 操作 2013-6-18

笔记301 触发器记录表某一个字段数据变化的日志 包括插入insert 修改update 删除delete 操作 2013-6-18

  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

 

posted @ 2013-08-04 21:17  桦仔  阅读(2869)  评论(0)    收藏  举报