数据库级别DML操作监控审计、表触发器/对象触发器

更多参考：https://www.cnblogs.com/gered/p/10812399.html

使用触发器记录DML，使用触发器记录表的DML

 数据库级别DML操作监控审计、表触发器/对象触发器

 

--核心参考
--SqlServer触发器 ，目的是记录操作内容
 begin     
      declare @di table(et varchar(200), pt varchar(200),  ei varchar(max))     
                    insert into @di exec('dbcc inputbuffer(@@spid)')   
      declare @op varchar(10)  select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)                   
              then 'Update'                    
              when exists(select 1 from inserted) and not exists(select 1 from deleted)                   
              then 'Insert'                   
              when not exists(select 1 from inserted) and exists(select 1 from deleted)                 
              then 'Delete' 
              end     
      if @op in('Update','Insert')    
         begin    
             insert into synchronization.dbo.synchro_log  (operate,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)      
             select @op,@@spid,         
                      (select login_name from sys.dm_exec_sessions where session_id=@@spid), 
                      (select program_name from sys.dm_exec_sessions where session_id=@@spid),         
                      (select hostname from sys.sysprocesses where spid=@@spid),         
                      (select client_net_address from sys.dm_exec_connections where session_id=@@spid),         
                      (select top 1 isnull(ei,'') from @di),  getdate()       
              from inserted n          
      end     
      else    
          begin      
                    insert into synchronization.dbo.synchro_log (operate,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)         
                    select @op,@@spid,           
                          (select login_name from sys.dm_exec_sessions where session_id=@@spid),           
                          (select program_name from sys.dm_exec_sessions where session_id=@@spid),          
                          (select hostname from sys.sysprocesses where spid=@@spid),          
                          (select client_net_address from sys.dm_exec_connections where session_id=@@spid),         
                          (select top 1 isnull(ei,'') from @di),          
                          getdate()        
            from deleted o    
      end  
end  

 

 

 

--创建记录表

CREATE TABLE T_SHALL_LOG (
    ID INT NOT NULL IDENTITY(1, 1)
    , EVTIME DATETIME NOT NULL DEFAULT(GETDATE())
    , --访问时间
    SQL VARCHAR(300)
    , --执行的SQL语句
    USERID VARCHAR(128) NOT NULL DEFAULT(SUSER_SNAME())
    , --连接的时候使用的哪个SQL登陆用户      
    HOSTNAME VARCHAR(128) NOT NULL DEFAULT(HOST_NAME())
    , --客户端的机器吿                       
    LOGINID VARCHAR(128)
    , --客户端的登陆用户（OS的用户）
    APPNAME VARCHAR(128) NOT NULL DEFAULT(APP_NAME())
    , --是从查询分析器，还是应用程序来执行的               
    OPTYPE INT
    , --执行的是INSERT,UPDATE,还是DELETE操作          
    ROWCNT INT --影响了多少行数据                                          
    )
GO



--创建测试表

CREATE TABLE T_SHALL (
    A INT
    , B VARCHAR(10)
    )
GO

---创建触发器

CREATE TRIGGER TR_T_SHALL ON T_SHALL --需要监控的表名
FOR INSERT
    , DELETE
    , UPDATE
AS
--ROWCNT 
DECLARE @ROWS INT

SET @ROWS = @@ROWCOUNT
SET NOCOUNT ON

--SQL 
CREATE TABLE #T (
    EVENTTYPE VARCHAR(20)
    , PARAMETERS INT
    , EVENTINFO VARCHAR(300)
    ) ----用临时表保存exec('DBCC INPUTBUFFER...')的返回倿

DECLARE @SPID VARCHAR(20)

SET @SPID = CAST(@@SPID AS VARCHAR)

INSERT #T
EXEC ('DBCC INPUTBUFFER (' + @SPID + ')')

--OPTYPE 
DECLARE @OPTYPE INT

SET @OPTYPE = 2 --UPDATE 

IF NOT EXISTS (
        SELECT 1
        FROM INSERTED
        )
    SET @OPTYPE = 3 --DELETE 

IF NOT EXISTS (
        SELECT 1
        FROM DELETED
        )
    SET @OPTYPE = 1 --INSERT

--进程信息
DECLARE @USERID VARCHAR(128)
    , --连接的时候使用的哪个SQL登陆用户      
    @HOSTNAME VARCHAR(128)
    , --客户端的机器吿                       
    @LOGINID VARCHAR(128)
    , --客户端的登陆用户（OS的用户）
    @APPNAME VARCHAR(128) --是从查询分析器，还是应用程序来执行的              

SELECT @USERID = LOGINAME
    , --连接的时候使用的哪个SQL登陆用户      
    @HOSTNAME = HOSTNAME
    , --客户端的机器吿                       
    @LOGINID = NT_USERNAME
    , --客户端的登陆用户（OS的用户）
    @APPNAME = PROGRAM_NAME --是从查询分析器，还是应用程序来执行的              
FROM MASTER..SYSPROCESSES
WHERE SPID = @@SPID

--INSERT 
INSERT T_SHALL_LOG (
    SQL
    , USERID
    , HOSTNAME
    , LOGINID
    , APPNAME
    , OPTYPE
    , ROWCNT
    )
SELECT EVENTINFO
    , @USERID
    , @HOSTNAME
    , @LOGINID
    , @APPNAME
    , @OPTYPE
    , @ROWS
FROM #T
GO



----DML操作测试
SELECT *
FROM t_shall_log;
SELECT *
FROM t_shall;

INSERT t_shall
VALUES (11, 'zhong'    );
INSERT t_shall
VALUES (12    , 'shall')

GO
INSERT t_shall
SELECT A + 2
    , B + '55'
FROM t_shall

GO

BEGIN TRANSACTION
    UPDATE t_shall
    SET A = 100    , B = '111'
    WHERE A = 11
COMMIT TRANSACTION

GO

DELETE t_shall WHERE A = 100

GO

SELECT * FROM t_shall_log;

SELECT * FROM t_shall;

INSERT dbo.T_SHALL
VALUES (1000, 'sa')

GO
DELETE dbo.T_SHALL

GO


--核验
select * from dbo.T_SHALL

go

select * from dbo.T_SHALL_LOG

go