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/