日志类触发器

 

触发器综述之二

 

日志类触发器

   

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

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

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

《一个用于跟踪和发现程序错误的触发器》是我之前贴出的一个触发器，用调用'DBCC INPUTBUFFER的方法记录了SQL语句，使用了临时表，有点影响速度。但没有记录影响行数和操作分类，今天贴一个全的，并贴出测试过程。客户端的IP的取得之前有过用XP_CMDSHELL调用PING的，但是这个方法与操作系统有关，有时测不到，而且对性能也有影响，所以就不检测了。

 

--建立日志表  

  CREATE   TABLE   TBLOG(  

  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   TBTEST(  

  A   INT,  

  B   VARCHAR(10)  

  )  

  GO  

   

  --触发器  

  CREATE   TRIGGER   TR_TBTEST   ON   TBTEST  

  FOR   INSERT,DELETE,UPDATE  

  AS  

   

  --ROWCNT  

  DECLARE   @ROWS   INT  

  SET   @ROWS=@@ROWCOUNT  

   

  SET   NOCOUNT   ON  

   

  --SQL