首页 > 代码库 > 触发器记录表某一个字段数据变化的日志

触发器记录表某一个字段数据变化的日志

<style>p.p1 { margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica } p.p2 { margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px "PingFang SC" } p.p3 { margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; min-height: 14.0px } span.s1 { font: 12.0px "PingFang SC" } span.s2 { font: 12.0px Helvetica }</style>

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

触发器记录表某一个字段数据变化的日志