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

文档供参考，可复制、编制，期待您的好评与关注！ 如何采用触发器跟踪对表的修改王黎明（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=2alter 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.TBZDKWFOR updateasbeginIf getdate()>'2014-03-01'-设置触发器的有效期BeginAlter table TBZDKW disable trigger TRI_TBZDKW-关闭触发器endDECLARE 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')endGODEMO:例子：脚本功能:把最后一次修改表里nsl字段的SQL语句保存下来DROP TABLE TRI_YPKCXGJL01DROP TABLE TRI_inputbuffer01drop trigger TRI_TBYPKC01select * into TRI_YPKCXGJL01 from master.sys.dm_exec_sessions where 1=2alter 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) -执行的SQLcreate table TRI_inputbuffer01(ceventtype varchar(100),cparameters varchar(100),ceventinfo varchar(2000)create trigger dbo.TRI_TBYPKC01 on dbo.TBYPKC01FOR updateasbeginIf getdate()>'2014-03-01'-设置触发器的有效期BeginAlter table TBYPKC01 disable trigger TRI_TBYPKC01-关闭触发器endIF UPDATE(nsl) -修改某个字段时才执行BEGIN DECLARE ikcbm int,cypbm varchar(40),nhsl decimal(16,3),nqsl decimal(16,3),dsj datetime,csql varchar(2000)- SELECT ikcbm=IKCBM,cypbm=CYPBM,nhsl=NSL,dsj=GETDATE() FROM inserted -修改后的值 SELECT nqsl=NSL FROM deleted -修改前的值 select csql='dbcc inputbuffer('+convert(varchar(10),SPID)+')' exec ('insert into TRI_inputbuffer01(ceventtype,cparameters,ceventinfo) exec('''+csql+''')') exec ('insert into TRI_YPKCXGJL01 select *,convert(varchar(20),'+ikcbm+'),'+cypbm+',convert(varchar(20),'+nqsl+'),convert(varchar(20),'+nhsl+'),GETDATE(),(select top 1 ceventinfo from TRI_inputbuffer01) from master.sys.dm_exec_sessions where session_id=SPID'); exec ('delete TRI_inputbuffer01')ENDendGO查看修改记录：SELECT * FROM TRI_YPKCXGJL01master.sys.dm_exec_sessions表字段说明：列名数据类型说明session_idsmallint标识与每个活动主连接关联的会话。不可为空值。login_timedatetime建立会话的时间。不可为空值。host_namenvarchar(128)特定于会话的客户端工作站名称。对于内部会话，该值为 NULL。可为空值。program_namenvarchar(128)初始化会话的客户端程序的名称。对于内部会话，该值为 NULL。可为空值。host_process_idint启动会话的客户端程序的进程 ID。对于内部会话，该值为 NULL。可为空值。client_versionint客户端连接到服务器所用接口的 TDS 协议版本。对于内部会话，该值为 NULL。可为空值。client_interface_namenvarchar(32)客户端连接到服务器所用的协议名称。对于内部会话，该值为 NULL。可为空值。security_idvarbinary(85)与登录名关联的 Microsoft Windows 安全 ID。不可为空值。login_namenvarchar(128)当前执行的会话所使用的 SQL Server 登录名。有关创建此会话的原始登录名，请参阅original_login_name。可以是经过 SQL Server 身份验证的登录名，也可以是经过 Windows 身份验证的域用户名。不可为空值。nt_domainnvarchar(128)客户端的 Windows 域（如果使用 Windows 身份验证或可信连接进行会话）。对于内部会话和非域用户，该值为 NULL。可为空值。nt_user_namenvarchar(128)客户端的 Windows 用户名（如果使用 Windows 身份验证或可信连接进行会话）。对于内部会话和非域用户，该值为 NULL。可为空值。statusnvarchar(30)会话的状态。可能的值：· 运行 - 当前正在运行一个或多个请求· 睡眠 - 当前没有运行任何请求· 休眠 会话因连接池而被重置，并且现在处于登录前状态。不可为空值。context_infovarbinary(128)会话的 CONTEXT_INFO 值。用户使用 SET CONTEXT_INFO 语句设置上下文信息。可为空值。cpu_timeint该会话所占用的 CPU 时间（毫秒）。不可为空值。memory_usageint该会话所占用的 8 KB 内存页数。不可为空值。total_scheduled_timeint计划内含请求的会话的执行所耗用的总计时间（毫秒）。不可为空值。total_elapsed_timeint自会话建立以来已耗用的时间（毫秒）。不可为空值。endpoint_idint与会话关联的端点的 ID。不可为空值。last_request_start_timedatetime最近一次会话请求的开始时间。这包括当前正在执行的请求。不可为空值。last_request_end_timedatetime最近一次会话请求的完成时间。可为空值。readsbigint在该会话期间该会话中的请求所执行的读取次数。不可为空值。Writesbigint在该会话期间该会话中的请求所执行的写入次数。不可为空值。logical_readsbigint已对该会话执行的逻辑读取数。不可为空值。is_user_processbit如果会话是系统会话，则为 0。否则，为 1。不可为空值。text_sizeint会话的 TEXTSIZE 设置。不可为空值。languagenvarchar(128)会话的 LANGUAGE 设置。可为空值。date_formatnvarchar(3)会话的 DATEFORMAT 设置。可为空值。date_firstsmallint会话的 DATEFIRST 设置。不可为空值。quoted_identifierbit会话的 QUOTED_IDENTIFIER 设置。不可为空值。arithabortbit会话的 ARITHABORT 设置。不可为空值。ansi_null_dflt_onbit会话的 ANSI_NULL_DFLT_ON 设置。不可为空值。ansi_defaultsbit会话的 ANSI_DEFAULTS 设置。不可为空值。ansi_warningsbit会话的 ANSI_WARNINGS 设置。不可为空值。ansi_paddingbit会话的 ANSI_PADDING 设置。不可为空值。ansi_nullsbit会话的 ANSI_NULLS 设置。不可为空值。concat_null_yields_nullbit会话的 CONCAT_NULL_YIELDS_NULL 设置。不可为空值。transaction_isolation_levelsmallint会话的事务隔离级别。0 = 未指定1 = 未提交读取2 = 已提交读取3 = 可重复4 = 可序列化5 = 快照不可为空值。lock_timeoutint会话的 LOCK_TIMEOUT 设置。该值以毫秒计。不可为空值。deadlock_priorityint会话的 DEADLOCK_PRIORITY 设置。不可为空值。row_countbigint到目前为止会话返回的行数。不可为空值。prev_errorint会话返回的最近一个错误的 ID。不可为空值。original_security_idvarbinary(85)与 original_login_name 相关的 Microsoft Windows 安全 ID。不可为空值。original_login_namenvarchar(128)客户端用于创建此会话的 SQL Server 登录名。可以是经过 SQL Server 身份验证的登录名，也可以是经过 Windows 身份验证的域用户名。请注意，此会话在初次连接后可能已进行多次隐式或显式上下文切换。例如，如果使用了 EXECUTE AS。不可为空值。last_successful_logondatetime当前会话开始前 original_login_name 上一次成功登录的时间。last_unsuccessful_logondatetime当前会话开始前，original_login_name 上一次登录失败的时间。unsuccessful_logonsbigint在 last_successful_logon 和 login_time 之间 original_login_name 的登录失败次数。8 / 8