美文网首页SQL server
Sql Server-触发器实例-用触发器做表中字段的修改日志

Sql Server-触发器实例-用触发器做表中字段的修改日志

作者: 柠檬正在努力 | 来源:发表于2020-04-02 21:20 被阅读0次
    起因:

    某个客户因为出现了一个Bug:按道理来说有个考勤数据只需要申报一次,往后都会自动继续申报,有些人员的数据却总是要重新申报。经查后发现是某表某数据出现被不知名操作更新的现象,获取数据库以后无法复现出问题,而系统代码又比较庞大复杂,数据库更是存在许多存储过程、触发器、约束,无从入手。决定使用数据库日志进行排查复现问题。

    原理:

    创建一个针对目标表的日志表,对该表添加触发器,当该字段发生增删改的适合就触发该触发器纪录各参数,方便排查问题,找出是什么语句对数据进行了操作方便排查问题


    下面直接放代码:
    首先是日志表创建代码的实例

    IF EXISTS (SELECT 1 FROM sysobjects WHERE id = object_id('log_sto_Table') AND type = 'U')
        DROP TABLE log_sto_Table
    GO
    -- 建日志表
    create table log_sto_Table
    (logid int not null identity(1,1),  -- 日志序号(日志主键)
     operate varchar(10),               -- 操作类型 如Insert,Update,Delete.
     id int,                            -- 原表ID(主键) 
     old_de datetime,                   -- de字段旧值
     new_de datetime,                   -- de字段新值
     spid int not null,                 -- spid
     login_name varchar(100),           -- 登录名
     prog_name varchar(100),            -- 程序名
     hostname varchar(100),             -- 主机名
     ipaddress varchar(100),            -- IP地址
     runsql varchar(4000),              -- 执行的TSQL代码
     UDate datetime                     -- 操作日期时间
     constraint pk_logsto primary key(logid)
    )
    go
    

    然后是触发器的创建

    if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_sto_Table]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
    drop trigger tr_sto_Table
    -- 建跟踪触发器
    create trigger tr_sto_Table
    on TableInf after update,insert,delete
    as
    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 log_sto_Table
         (operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
         select @op,n.EmpID,o.Enabled,n.Enabled,@@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
         left join deleted o on o.EmpID=n.EmpID
       end
       else
       begin
         insert into log_sto_szy
           (operate,id,old_de,new_de,spid,login_name,prog_name,hostname,ipaddress,runsql,UDate)
           select @op,o.EmpID,o.Enabled,null,@@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
    go
    

    下面是图片实例,再次发生问题的时候很容易就定位到了某个存储过程,然后可以进行阅读排查,实际上发现问题是另一张表的某个触发器在手机端某些用户登录的时候在特定情况下会对该表数据进行操作


    image.png

    相关文章

      网友评论

        本文标题:Sql Server-触发器实例-用触发器做表中字段的修改日志

        本文链接:https://www.haomeiwen.com/subject/rvvfphtx.html