创建日志记录触发器记录某表的DML操作

最新推荐文章于 2022-04-25 15:50:34 发布
最新推荐文章于 2022-04-25 15:50:34 发布
阅读量2.9k 收藏
点赞数
文章标签： 数据库

1.日志触发器

    日志类触发器的功能是记录对表的操作，也就是尽量全面地反映数据库表所进行的INSERT,UPDATE,DELETE操作，便于日后翻阅。

    从功能上来说，一般此类触发器往往会建立一个日志表，用来记录操作的过程，往往需要记录操作者信息，比如SQL登陆用户、客户端的机器名、客户端的IP、OS登陆用户、所用的应用程序等。也需要记录操作时间、执行的SQL语句以及操作所影响的记录，对操作记录的记录，可以分为两类，一类是只记录操作类型和影响的行数，其中操作类型分为增加、删除和修改；另一类是详细记录插入后、修改前、修改后、删除前的记录，这种触发器在一次操作多条记录的时候会记录多条记录，修改的时候会产生两倍日志记录，记录更佳详细，但是对性能的影响也会大些。

    从技术上来说，这类触发器最简单，因为不管是记录操作类别还是记录改前、删前数据，触发器都只需做插入操作，但是需要记录的信息的得到有些是有点难度的，特别是客户端的IP和执行的SQL语句。

 

 

2.触发器日志收集测试

----创建一个测试库，然后切换到该库下面进行操作：

USE master

GO

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'yaxuan_db')

DROP DATABASE yaxuan_db

GO

CREATE DATABASE yaxuan_db

GO

 

----切换到yaxuan_db数据库中

USE yaxuan_db

GO

 

1.1.创建触发器记录日志表

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  

 

1.2.创建一张测试表

----后面创建一个触发器用于跟踪该表的ddl操作

CREATE   TABLE   T_SHALL(  

  A   INT,  

  B   VARCHAR(10)  

  )  

  GO  

 

1.3 创建触发器

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  

 

1.4 执行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 yaxuan_db.dbo.T_SHALL values(1000,'sa')

go

delete yaxuan_db.dbo.T_SHALL

go

----查看结果

select * from yaxuan_db.dbo.T_SHALL

go

select * from yaxuan_db.dbo.T_SHALL_LOG

go

 

----批量更新

declare @i int

set @i=1

while @i<10000

begin

insert t_shall values(@i,'xue')

set @i=@i+1

end

 

 

 

 

 

3.更详细的触发器日志收集

    上面是比较通用的跟踪操作类型的例子，从结果看，SQL语句有时会测到SP_EXECUTESQL;1之类的语句，也只能测到255个字节，如果语句太长会被截断，OS用户有时测不出来，是因为网络设置，没有用域管理，其他方面应该是比较理想的，我们可以自己增加筛选条件，来减少记录数据的数据量，比如只记录删改数据。

 

----先清除之前的信息,并创建记录日志表

DROP TABLE T_SHALL_LOG

GO

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,     --影响了多少行数据                                         

  DATE1  VARCHAR(100),   --监控表中的A列的值

  DATE2  VARCHAR(100)    --监控表中B列的值                                                                      

  ) 

  GO  

 

truncate table t_shall_log

go

select * from t_shall_log

go

 

----创建触发器监控

CREATE   TRIGGER   TR_TBTEST1   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)  

  )  

   

  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

   

  IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[TBLOG1]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)    --日志表是否存在

  INSERT     t_shall_log(EVTIME,SQL,USERID,HOSTNAME,LOGINID,APPNAME,OPTYPE,ROWCNT,DATE1,DATE2)

  SELECT

       EVTIME      =GETDATE(),                         --访问时间 

       SQL   =T.EVENTINFO,                          --执行的SQL语句 

       USERID      =@USERID,                            --连接的时候使用的哪个SQL登陆用户       

       HOSTNAME=@HOSTNAME,                  --客户端的机器名                         

       LOGINID =@LOGINID,                               --客户端的登陆用户（OS的用户） 

       APPNAME     =@APPNAME,          --是从查询分析器，还是应用程序来执行的               

       OPTYPE      =CASE @OPTYPE WHEN 2 THEN 4 ELSE @OPTYPE END,   --记录类型,1—INSERT记录,2—UPDATE前记录,3—DELETE前记录,4—UPDATE后记录           

       ROWCNT      =@ROWS,                           --影响了多少行数据                                           

      D.*                                                   --改前或者删除前记录

  FROM       DELETED D,#T T

  UNION      ALL

  SELECT

       EVTIME      =GETDATE(),                         --访问时间 

       SQL   =T.EVENTINFO,                          --执行的SQL语句 

       USERID      =@USERID,                            --连接的时候使用的哪个SQL登陆用户       

       HOSTNAME=@HOSTNAME,                  --客户端的机器名                         

       LOGINID =@LOGINID,                               --客户端的登陆用户（OS的用户） 

       APPNAME     =@APPNAME,          --是从查询分析器，还是应用程序来执行的               

       OPTYPE      =@OPTYPE,                   --记录类型,1—INSERT记录,2—UPDATE前记录,3—DELETE前记录,4—UPDATE后记录        

       ROWCNT      =@ROWS,                           --影响了多少行数据                                           

      I.*                                                   --改后或者新插入记录

  FROM       INSERTED I,#T T

 

  ELSE

  SELECT

       EVTIME      =GETDATE(),                         --访问时间 

       SQL   =T.EVENTINFO,                          --执行的SQL语句 

       USERID      =@USERID,                            --连接的时候使用的哪个SQL登陆用户       

       HOSTNAME=@HOSTNAME,                  --客户端的机器名                         

       LOGINID =@LOGINID,                               --客户端的登陆用户（OS的用户） 

       APPNAME     =@APPNAME,          --是从查询分析器，还是应用程序来执行的               

       OPTYPE      =CASE @OPTYPE WHEN 2 THEN 4 ELSE @OPTYPE END,   --记录类型,1—INSERT记录,2—UPDATE前记录,3—DELETE前记录,4—UPDATE后记录           

       ROWCNT      =@ROWS,                           --影响了多少行数据                                           

      D.*                                                   --改前或者删除前记录

  INTO TBLOG1

  FROM       DELETED D,#T T

  UNION      ALL

  SELECT

       EVTIME      =GETDATE(),                         --访问时间 

       SQL   =T.EVENTINFO,                          --执行的SQL语句 

       USERID      =@USERID,                            --连接的时候使用的哪个SQL登陆用户       

       HOSTNAME=@HOSTNAME,                  --客户端的机器名                         

       LOGINID =@LOGINID,                               --客户端的登陆用户（OS的用户） 

       APPNAME     =@APPNAME,          --是从查询分析器，还是应用程序来执行的               

       OPTYPE      =@OPTYPE,                   --记录类型,1—INSERT记录,2—UPDATE前记录,3—DELETE前记录,4—UPDATE后记录        

       ROWCNT      =@ROWS,                           --影响了多少行数据                                           

      I.*                                                   --改后或者新插入记录

  FROM       INSERTED I,#T T

   

  GO  

 

 

----测试

insert into t_shall values(101,'username')

go

insert into t_shall values(102,'username')

go

update t_shall set a=109 where b='username'

go

select * from t_shall

go

select * from t_shall_log

go

 

 

 

4.总结

    日志类触发器我们也可以叫做跟踪类触发器，主要用于按自己的需要记录自己关心的一些数据库操作，其作用有：

    a.在编码和测试阶段，可以用来发现编程错误和疏忽。

    b.记录详细数据日志的触发器，可以用来分析系统流程和数据变化的细节，从而发现和修正业务流程某些细节设计的问题。

    c.在维护阶段可以用来排查问题原因，也可以用来监测是否有非法修改数据的情况。

   

    日志类触发器编写的主要难点在于关于操作的各种信息的取得和筛选，很多信息需要访问系统表或者使用系统函数，需要有比较大的权限，在一些权限控制比较严的系统，用访问系统表获得系统信息的方法可能并不适用，这是最好在数据表有记录操作者信息如用户名的字段。上面的例子都没有写筛选的代码，筛选需要根据信息的取得方法来决定，上面例子只要加几个判断退出语句就可以实现筛选。

 

 

 

reference   http://blog.csdn.net/haiwer/article/details/3016270

http://blog.csdn.net/Haiwer/article/category/322848

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2124546/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2124546/

cuimiwan3140
关注
使用触发器对学生表操作进行日志记录
thetheflower的博客
12-25 4167
项目介绍 触发器，就是一种特殊的存储过程，触发器和存储过程一样是一个能够完成特定功能，存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动调用。 学生信息表（stu）：记录学生了的学号，姓名，性别，年龄 日志信息表（stu_log）：记录对学生信息的操作（记录在xx时间对xx学生进行了xx操作） 当向stu表中进行增加删除修改学生信息时，同时要在stu_log表中添加一条操作日志 1、NEW与OLD关键字介绍 触发器用于监听对数据
触发器生成数据库表操作日志
03-03
【触发器生成数据库表操作日志】是一种技术手段，用于记录数据库中特定数据表的DML（数据操作语言）变化，如INSERT、DELETE和UPDATE操作。这种做法在数据库管理中非常常见，尤其对于审计和追踪数据变更历史至关重要...
用触发器生成数据库表的数据操作日志
03-04
本文将介绍如何使用触发器，把DML（数据操作语言）对数据库中的特定数据表的改动记录下来。因为我们要写入文件系统，安全权限开始有影响了。所以，执行插入操作的用户必须具备该文本文件的读写权限。因此，设计一个C／S结构的应用程序供多用户运行，或许不是一个可行的解决方案。更合理的方案是，设计一个三层应用程序，由你的中 间层组件对单用户数据库进行调用。
oracle触发器记录所有dml,使用Trigger审计一张表的DML操作
weixin_33980343的博客
04-02 278
最近ogg的灾备端复制进程中的一张表老是报错ORA-04031，但是又查不到原因，于是想用审计的方法来看到底这张表是被谁做了DML操作，把数据搞没了。本来想用数据库自带的审计功能参考：http://www.jb51.cc/article/p-zchlscoj-xe.html但是需要重启数据库，就放弃了，上网查资料看到有人用触发器来实现这个功能，于是自己也做了尝试。平台11.2.0.4sys@ORC...
Oracle通过触发器审计一张表的DML
justaiiit的博客
08-26 628
1.场景描述： 工作中，OGG数据同步的时候发现目标端B库有部分update操作没有同步过去，通过logminer挖掘源端A库发现没同步的操作是ogg配置用户执行操作的，OGG抽取进程中又设置了TRANLOGOPTIONS EXCLUDEUSER参数（防止出现级联同步）排除了OGG配置用户的操作，通过查看发现该同步表未从其他库同步过来（即A库是这张同步表最原始的数据来源）。然而logminer挖掘出来的os_username，machine_name和session_info都是unkown。通过表审计可
Oracle数据库,当DML操作时执行触发器记录日志
小静的专栏
01-21 1675
一图搞定 看图说话 老师都这么说:
使用触发器操作表1(添加，更新，删除) 同步实现表2的操作
ws_hgo的专栏
09-15 3188
使用触发器操作表1(添加，更新，删除) 同步实现表2的操作
利用SQL Server触发器实现表的历史修改痕迹记录
09-08
在SQL Server中，触发器是一种特殊的存储过程，它在特定的DML操作（INSERT、UPDATE、DELETE）执行后自动触发，常用于实现复杂的业务规则或数据完整性约束。本例主要探讨如何利用SQL Server的触发器来记录表的历史...
oracle触发器记录所有dml,oracle 触发器的种类和触发事件,DML触发器,DDL事件触发器,替代触发器,查看触发......
weixin_36129381的博客
04-02 458
触发器的种类和触发事件触发器必须由事件才能触发。触发器的触发事件分可为3类，分别是DML事件、DDL事件和数据库事件。每类事件包含若干个事件，如下所示。数据库的事件是具体的，在创建触发器时要指明触发的事件。种 类 关 键 字 含 义Sql代码 DML事件(3种)INSERT在表或视图中插入数据时触发UPDATE修改表或视图中的数据时触发DELETE在删除表或视图中的数据时触发DDL事件(...
触发器记录新增和修改操作.txt
07-05
根据用户新增和修改操作，触发器自动生成记录，用来记录用户的操作。（说明展开表，找到触发器右击新建触发器即可-新增和修改要分开创建）
oracle触发器记录所有dml,Oracle DML类型触发器
weixin_36278982的博客
04-02 400
Oracle DML类型触发器Oracle DML类型触发器是Oracle开发过程当中最经常用到，也是最常见的触发器，主要是对DML操作，如：insert、delete、update操作事件进行触发。DML类型触发器安装触发的事件的前后和数据触发的类型可以分为四类：前置行级触发器、后置行级触发器、前置语句级触发器、后置语句级触发器。创建DML类型触发器的语法结构如下：create[orrep...
用触发器写日志记录
weixin_42494845的博客
05-03 3245
首先先创建一个学生表 create table student(id int primary key auto_increment,name varchar(20),sex enum("male","female"),age int); 创建一个触发器，不允许年龄小于0或者大于100 delimiter % create trigger student_age before insert on ...
mysql建日志表_创建日志表记录DML操作和DDL操作
weixin_42361153的博客
02-07 957
创建一个日志表，记录dept表的DML操作create table dept_log(logid number,type varchar2(50),logdate date,deptno number,dname varchar2(50),loc varchar2(50));create sequence dept_log_seq;create or replace trigger dept_up...
使用触发器实现对表的DML操作
qq_26698925的博客
04-25 379
实现对列的操作更新，删除，插入操作后进行记录： 创建记录插入，删除，更新的表： CREATE TABLE “HISDB”.“HISDB”.“TEST_TABLE”( “OP_TIME” TIMESTAMP(6), “OCCUR_TIME” TIMESTAMP(6), “OP_TYPE” VARCHAR(50), CONSTRAINT “INDEX33555500” PRIMARY KEY(“OP_TIME”)) STORAGE( INITIAL 1 , NEXT 1 , MINEXTENTS 1 , on
oracle9i自动通过trigger捕获DML 语句(zz)
bjguan's BLOG
03-24 1797
  oracle9i自动通过trigger捕获DML 语句    
oracle跨库实现ddl,如何实现trigger集中记录所有库ddl操作
weixin_42524100的博客
04-05 277
今天客户说了一个我感觉有意思的需求:在一个库上的一张表记录所有库的ddl操作,实现方式：在一个库上建立表和触发器,其他库上通过dblink+同义词+触发器实现ddl操作记录到远程的表中.他当时写了一个触发器,但是有错误,想让我协助解决.在我们的一起努力下,解决了该触发器在dblink同义词的库上出错的问题.我这里测试使用的是10g的库做为存储所有库的ddl记录的库,11g库做为一个通过dblink...
oracle触发器审计视频,【学习笔记】Oracle审计 使用trigger触发器监控所有数据库DDL操作的方法...
weixin_30342363的博客
04-03 282
天萃荷净实现trigger集中记录所有库ddl操作，运维DBA反映用户需求，监控所有Oracle数据库的DDL语句并存入数据表中。通过trigger触发器实现对所有数据库的DDL语句记录分析用户需求:在一个库上的一张表记录所有库的ddl操作,实现方式：在一个库上建立表和触发器,其他库上通过dblink+同义词+触发器实现ddl操作记录到远程的表中.他当时写了一个触发器,但是有错误,想让我协助解决....
创建监控表的DML的触发器
cqwf95925的博客
06-21 146
在生产环境中，总是可能出现这样的情况：某张或者某些表的数据被莫名其妙的修改了，但是很难定位出是哪个用户、哪个过程修改的。这是一个很让DBA头痛的事情（往往DBA对于整个代码逻辑并不是非常了解）。要定位出“问题”语句，有几种方法可...
数据库日志记录：使用触发器跟踪DML操作
"使用触发器生成数据库表的数据操作日志是数据库管理中的一种重要技术，用于记录DML（数据操作语言）对特定表的修改。这有助于跟踪和审计数据变化，对于确保数据安全和满足合规性要求至关重要。本文通过一个实例展示...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值