如何采用触发器跟踪对表的修改
王黎明（2014-03-25 11:21）
目标：
通过触发器把修改表字段的SQL语句保存下来。
基础知识：
表和控制台命令说明：
master.sys.dm_exec_sessions 是服务器范围的视图，显示了有关所有活动用户连接和内部任务的信息。 此信息包含客户端版本、客户端程序名称、客户端登录时间、登录用户、当前会话设置等。
使用 master.sys.dm_exec_sessions，首先可以查看当前的系统负荷并标识相关会话，然后可以通过其他动态管理视图或动态管理函数了解有关该会话的详细信息。
字段说明见文档最后的字段说明。
dbcc inputbuffer(spid)：显示从客户端发送到 Microsoft SQL Server 的最后一个语句。
@@SPID：当前用户连接数据库进程ID
注意：增加字段的sql语句
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 decimal(16,3) --执行SQL修改后的数量
alter table TRI_YPKCXGJL01 add dsj datetime  --修改时间
alter table TRI_YPKCXGJL01 add csql varchar(2000)  --执行的SQL
create table TRI_inputbuffer01(ceventtype varchar(100),cparameters varchar(100),ceventinfo varchar(2000))
create trigger [dbo].[TRI_TBYPKC01] on [dbo].[TBYPKC01]
FOR update
as
begin
    If getdate()>'2014-03-01'--设置触发器的有效期
Begin
Alter table TBYPKC01 disable trigger TRI_TBYPKC01--关闭触发器
end
IF UPDATE(nsl) --修改某个字段时才执行
    BEGIN     
      DECLARE @ikcbm int,@cypbm varchar(40),@nhsl decimal(16,3),@nqsl decimal(16,3),@dsj datetime,@csql varchar(2000)--

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。