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

本文参考:http://www.cnblogs.com/lyhabc/articles/3236985.html

 

--触发器记录表某一个字段数据变化的日志 包括插入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

 

 改进版本:1、只记录Update字段时，新老值不一致情况。注意NULL是不能进行比较。需要用到Isnull进行转换后，方可进行比较操作。

 

-- 建跟踪触发器
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