触发器监控SQL操作

USE [lsCommon]
GO
/****** 对象:  Trigger [dbo].[tr_domainSvr]    脚本日期: 01/06/2014 08:37:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- ITS域服务器日志触发器
-- =============================================
ALTER TRIGGER [dbo].[tr_domainSvr]
   ON  [dbo].[tbl_wf_ITS_domainSvr]
   FOR INSERT,DELETE,UPDATE
--   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS 
BEGIN
    SET NOCOUNT ON;
--用临时表保存exec('DBCC INPUTBUFFER...')的返回值
create table #tzy(EventType varchar(50),Parameters int ,EventInfo varchar(6000))
----DBCC INPUTBUFFER需要参数spid
declare @spid varchar(20)
set @spid=cast(@@spid as varchar)
insert #tzy exec('DBCC INPUTBUFFER ('+@spid+') WITH NO_INFOMSGS')
declare @info nvarchar(max)
declare @staff_id varchar(8)
declare @domainSvrSeq varchar(50)
declare @domainSvrName nvarchar(100)
declare @domainSvrRemark nvarchar(MAX)
declare @stat char(1)
declare @domainSvrSeq_d varchar(50)
declare @domainSvrName_d nvarchar(100)
declare @domainSvrRemark_d nvarchar(MAX)
declare @stat_d char(1)
DECLARE @IP_Address varchar(255)
--SET @staff_id = (select updateBy from inserted)
--SET @info = (select updateBy from inserted)
SET @staff_id = ''

select @domainSvrSeq = domainSvrSeq
       ,@domainSvrName = domainSvrName
       ,@domainSvrRemark = domainSvrRemark
       ,@stat = [status]
       ,@staff_id = updateBy
from inserted

select @domainSvrSeq_d = domainSvrSeq
       ,@domainSvrName_d = domainSvrName
       ,@domainSvrRemark_d = domainSvrRemark
       ,@stat_d = [status]
from deleted

select @info = #tzy.EventInfo from #tzy

--如果是后台修改数据的情况
if (@info = 'lsCommon.dbo.stp_wf_ITS_adminSetDomainSvr_V2;1') 
begin

IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
BEGIN
    insert into tbl_wf_ITS_log (log_info,log_updateTime,log_updateBy)
    values (N'域服务器表新增记录:' + @domainSvrSeq + ',' + @domainSvrName + ',' + @domainSvrRemark + ',' + @stat + '.',getdate(),@staff_id)
END


IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
BEGIN
    insert into tbl_wf_ITS_log(log_info,log_updateTime,log_updateBy)
    values (N'域服务器表更新记录:' + @domainSvrSeq + ',' + @domainSvrName + ',' + @domainSvrRemark + ',' + @stat + '.',getdate(),@staff_id)
END


IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
BEGIN
    insert into tbl_wf_ITS_log(log_info,log_updateTime,log_updateBy)
    values (N'域服务器表删除记录:'+ @domainSvrSeq_d + ',' + @domainSvrName_d + ',' + @domainSvrRemark_d + ',' + @stat_d + '.',getdate(),@staff_id)
END

--insert tbl_wf_ITS_log(log_info,log_updateTime,log_updateBy)
--values (ltrim(rtrim(@info)) + ',' + @domainSvrName + ',' + @domainSvrRemark + ',' + @stat + '.',getdate(),@staff_id)
end
ELSE --如果是用SQL客户端来修改
begin

----获取客户端IP
    SELECT @IP_Address = client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID
----客户端用户名
    SET @staff_id = SYSTEM_USER
----最后一次SQL操作
----select @info = #tzy.EventInfo from #tzy
    set @info = '[' + @IP_Address + ']' + N'执行SQL:' + @info
insert into tbl_wf_ITS_log (log_info,log_updateTime,log_updateBy)
values (ltrim(rtrim(@info)),getdate(),@staff_id)    
--select @staff_id
--select @IP_Address
--select @info 
--select * from #tzy

end



--select @info 
--select * from #tzy

--CREATE FUNCTION [dbo].[GetCurrentIP] ()
--RETURNS varchar(255)
--AS
--BEGIN
--    DECLARE @IP_Address varchar(255);
-- 
--    SELECT @IP_Address = client_net_address
--    FROM sys.dm_exec_connections
--    WHERE Session_id = @@SPID;
-- 
--    Return @IP_Address;
--END




--if (@staff_id = '')
--Begin
-- --SET @staff_id = SYSTEM_USER
-- --exec('DBCC INPUTBUFFER ('+@spid+')')
-- select @info = #tzy.EventInfo from #tzy
----drop table #tzy
--END

--IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
--BEGIN
--    insert tbl_wf_ITS_log(log_info,log_updateTime,log_updateBy)
--    values (N'域服务器表新增记录:' + @domainSvrSeq + ',' + @domainSvrName + ',' + @domainSvrRemark + ',' + @stat + '.',getdate(),@staff_id)
--END
--
--
--IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
--BEGIN
--    insert tbl_wf_ITS_log(log_info,log_updateTime,log_updateBy)
--    values (N'域服务器表更新记录:' + @domainSvrSeq + ',' + @domainSvrName + ',' + @domainSvrRemark + ',' + @stat + '.',getdate(),@staff_id)
--END
--
--
--IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
--BEGIN
--    insert tbl_wf_ITS_log(log_info,log_updateTime,log_updateBy)
--    values (N'域服务器表删除记录:'+ @domainSvrSeq_d + ',' + @domainSvrName_d + ',' + @domainSvrRemark_d + ',' + @stat_d + '.',getdate(),@staff_id)
--END





END

----------------------------------------------------------------------------------------------------------------------------------------------

 

触发器记录SQL操作记录