(通过触发器跟踪对表的修改SQL语句.docVIP

如何采用触发器跟踪对表的修改 王黎明（2014-03-25 11:21） 目标： 通过触发器把修改表字段的SQL语句保存下来。 基础知识： 表和控制台命令说明： master.sys.dm_exec_sessions 是服务器范围的视图，显示了有关所有活动用户连接和内部任务的信息。 此信息包含客户端版本、客户端程序名称、客户端登录时间、登录用户、当前会话设置等。 使用 master.sys.dm_exec_sessions，首先可以查看当前的系统负荷并标识相关会话，然后可以通过其他动态管理视图或动态管理函数了解有关该会话的详细信息。 字段说明见文档最后的字段说明。 dbcc inputbuffer(spid)：显示从客户端发送到 Microsoft SQL Server 的最后一个语句。 @@SPID：当前用户连接数据库进程ID 注意： 1：master.sys.dm_exec_sessions在SQL2008R2和SQL2012服务器里表字段不一样 2：dbcc inputbuffer()执行后返回的结果没法直接查询和保存，只有通过创建个表来中转数据 3：因为此触发器涉及的数据比较多，影响数据库执行效率，所以用于一段时间的数据监控，检测完后就关闭此触发器 触发器： select * from sys.triggers where name=TRI_name sp_helptext TRI_TBZDKW 查看触发器内容 --1:根据需要的信息创建一张表（TRI_TBZDKWXGJL）表的名称建议不要用TB开头，用TRI_开头来区分我们的HIS数据表名，用于保存最后一次执行SQL的记录，表字段按需要的信息修改 select * into TRI_TBZDKWXGJL from master.sys.dm_exec_sessions where 1=2 alter table TRI_TBZDKWXGJL add csql varchar(2000) --执行的SQL --2：创建TRI_inputbuffer 用于保存SQL语句 create table TRI_inputbuffer(ceventtype varchar(100),cparameters varchar(100),ceventinfo varchar(2000)) --3：删除触发器 drop trigger [TRI_TBZDKW] --4：创建触发器 （具体语法参照SQL帮助文档，这里不做说明） create trigger [dbo].[TRI_TBZDKW] on [dbo].[TBZDKW] FOR update as begin If getdate()2014-03-01--设置触发器的有效期 Begin Alter table TBZDKW disable trigger TRI_TBZDKW--关闭触发器 end DECLARE @csql varchar(2000) select @csql=dbcc inputbuffer(+convert(varchar(10),@@SPID)+) exec (insert into TRI_inputbuffer(ceventtype,cparameters,ceventinfo) exec(+@csql+)) exec (insert into TRI_TBZDKWXGJL select *,(select top 1 ceventinfo from TRI_inputbuffer) from master.sys.dm_exec_sessions where session_id=@@SPID); exec (delete TRI_inputbuffer) end GO DEMO: 例子： 脚本功能:把最后一次修改表里nsl字段的SQL语句保存下来 DROP TABLE TRI_YPKCXGJL01 DROP TABLE TRI_inputbuffer01 drop trigger [TRI_TBYPKC01] select * into TRI_YPKCXGJL01 from master.sys.dm_exec_sessions where 1=2 alter table TRI_YPKCXGJL01 add ikcbm int --修改的药品库存编码 alter table TRI_YPKCXGJL01 add cypbm varchar(40) --药品编码 alter table TRI_YPKCXGJL01 add nqsl decimal(16,3) --修改前的数量 alter table TRI_YPKCXGJL01 add nhsl deci