在有些公司中，由于管理的不规范，或者是便于开发人员直接修改、部署程序，往往任何开发人员，都能登录到生产环境的数据库直接进行操作。

当然啦，一般的select语句可能不会导致什么太大的问题，但如果是delete和update，而由于某些开发人员忘记写where条件，或者条件写错了，导致一下子删除或更新了，导致数据出了问题，最关键的是当发现问题后，没有人承认这个是他干的，那该怎么办呢？

下面就是通过DML触发器，来监控哪些IP的机器，在什么时候进行了update操作：

--要跟踪的表







if OBJECT_ID('wc') is not null







   drop table wc







go























create table wc(id int,vv varchar(10))















insert into wc







select 1,'abc' union all







select 2,'abc' union all







select 3,'abc'







go























--存放审核信息的表







if OBJECT_ID('audit_table') is not null







   drop table audit_table







go















create table audit_table







(







id int identity(1,1) primary key,















spid int,







operation nvarchar(20),







exec_sql nvarchar(max),















connect_time DATETIME,







net_transport NVARCHAR(40),







protocol_type NVARCHAR(40),







auth_scheme NVARCHAR(40),







client_net_address VARCHAR(48),







client_tcp_port int,







local_net_address VARCHAR(48),







local_tcp_port int,















host_name NVARCHAR(128),







program_name NVARCHAR(128),







host_process_id int,







client_interface_name NVARCHAR(32),















exec_datetime datetime







)







go























--触发器







create trigger dbo.trigger_wc







on wc







after update







as















declare @t table(eventtype nvarchar(14),prama smallint,eventinfo nvarchar(max))















insert into @t(eventtype,prama,eventinfo)







exec('DBCC INPUTBUFFER('+@@spid+')')















insert into audit_table







select  @@SPID,







        'UPDATE',







        (select eventinfo from @t),







        (select connect_time from sys.dm_exec_connections where session_id = @@spid),







		(select net_transport from sys.dm_exec_connections where session_id = @@spid),







		(select protocol_type from sys.dm_exec_connections where session_id = @@spid),







		(select auth_scheme from sys.dm_exec_connections where session_id = @@spid),







		(select client_net_address from sys.dm_exec_connections where session_id = @@spid),







		(select client_tcp_port from sys.dm_exec_connections where session_id = @@spid),







		(select local_net_address from sys.dm_exec_connections where session_id = @@spid),







		(select local_tcp_port from sys.dm_exec_connections where session_id = @@spid),















		(select [host_name] from sys.dm_exec_sessions where session_id = @@spid),







		(select [program_name] from sys.dm_exec_sessions where session_id = @@spid),







		(select host_process_id from sys.dm_exec_sessions where session_id = @@spid),







		(select client_interface_name from sys.dm_exec_sessions where session_id = @@spid),







        GETDATE()







go















--更新数据







update wc







set vv = 'aaa'























--查看记录







select *    







from audit_table

下面是监控结果：