触发器跟踪表字段更改记录

       在实际生产业务中，时常会遇到业务系统进行了操作，但后台表相关字段未做修改；往往是操作已变更相关字段，但另外的业务将后台表的字段修改回来。在这种情况查找改变回来操作的查找，几乎是大海捞针，所以此时建立表触发器对相关字段进行跟踪成了绝妙的方法。
       以下是建立触发器的测试用例：

1.创建测试表

create table TABLE_TEST
(
ID INT,
NAME VARCHAR(100)
)

2.创建跟踪表

CREATE TABLE temp_update_log(
	tablename varchar(100) null,  
	[czlb] varchar(32) null,    -- 删除、更新、插入
	spid int null,  --进程ID
	hostname varchar(128) null,  --操作电脑名称
	program_name varchar(128) null,--应用程序名称
	login_name varchar(128) null,--数据库登录账户
	net_address varchar(128) null,--操作电脑IP
	sql varchar(8000) null,--SQL脚本
	czrq datetime null     --操作日期
)

3.创建触发器

--删除触发器
--drop trigger tr_table_test_update

--创建表TABLE_TEST更新触发器
create trigger tr_table_test_update on TABLE_TEST for update
as
/**********
**********/
set nocount on

	if exists (select * from sysobjects where type = 'U' and name = 'temp_update_log')
	begin

			if UPDATE(NAME) 	--更新[NAME]字段跟踪
			begin

			                if  exists(select 1 from  deleted where ISNULL([NAME],'')='')   --字段 [NAME]空值跟踪
						       return

			                if not exists(select 1 from  inserted where ISNULL([NAME],'')='')   --字段 [NAME]空值跟踪
						      return


							declare 
								@spid int,
								@hostname varchar(100) ,
								@program_name varchar(100) ,
								@login_name varchar(100) ,
								@net_address varchar(100) ,
								@sql varchar(2000) 
							select @spid=@@SPID

							SELECT 
							--@sql=substring(dest.text,1,512)
							@login_name=des.login_name 
							,@program_name=des.[program_name]
							,@hostname=des.host_name 
							,@net_address=dec.client_net_address 

							FROM master.sys.dm_exec_sessions des(nolock) 
							INNER JOIN  master.sys.dm_exec_connections dec (nolock) 
							ON des.session_id = dec.session_id
							INNER JOIN  master.sys.sysprocesses prc (nolock) 
							ON des.session_id = prc.spid 
							WHERE des.is_user_process = 1   AND des.session_id = @spid 

	      					Create TABLE #Tsql( EventType nvarchar(1000),Parameters int, EventInfo nvarchar(4000)) 
							insert into #Tsql  exec('dbcc inputbuffer(' + @spid + ')')
							select @sql=substring(EventInfo,1,512) from #Tsql

							insert into temp_update_log(
										tablename
										,czlb,spid,hostname,program_name,login_name,net_address,sql,czrq) 
								select 'TABLE_TEST','update',@spid,@hostname,@program_name,@login_name,@net_address,@sql,getdate() 
								from deleted 
								if @@error <>0
								begin
								   return
								end			
			end
				
	end
    return

4.测试

insert into TABLE_TEST values(1,'张三')

一.语句跟新
update TABLE_TEST set NAME=''

查询结果
    select * from temp_update_log

二.存储更新
   第一步：创建存储
   create proc pro_test
   as
    update TABLE_TEST set NAME='' 

    第二步：调用存储
    exec pro_test

    查询结果
    select * from temp_update_log

测试效果