用触发器生成数据库表的数据操作日志

作为一名数据库管理员，你尽力以各部门熟知的不同格式，向各部门提供它们所需要的数据。你通常将MS Excel格式的数据递交到会计部门，或将数据以HTML报表的形式呈现给普通用户。你们的系统安全管理员们则习惯于用文本阅读器或者事件查看器来查看日志。本文将介绍如何使用触发器，把DML（数据操作语言）对数据库中的特定数据表的改动记录下来。注：下列例子为Insert型触发器，不过改成Delete／Update型的触发器也很容易。

操作步骤首先让我们在Northwind数据库内创建一个简单表。

create table tablefortrigger
(
　track int identity(1,1) primary key,
　Lastname varchar(25),
　Firstname varchar(25)
)

创建好这个数据表后，添加一个标准message到master数据库的sysmessages数据表中。注意，我所添加的是一个参变量，用以接受一个字符值，它将被输出显示给管理员们。通过设置@_with_log参数为true，我们包管相关结果被发送到事件日志。

sp_addmessage 50005, 10, ''%s'', @with_log = true

现在我们创建这条用有意义的信息填充的消息。下面的信息将填充这条消息，并且记录到文件中：

·操作的类型（插入）。

·受到影响的数据表。

·改动的日期与时间。

被该语句插入的全部字段。 下面的这个触发器用预定义值（1～3个字符）创建一个字符串，该预定义值位于inserted数据表中。（这个inserted数据表驻留在内存中，它容纳被插入到触发器所在数据表的记录行）。触发器连接这些值并放到一个@msg变量。然后这个变量被传送到raiserror函数，该函数将它写到事件日志中。

Create trigger TestTrigger on
tablefortrigger
for insert
as

--声明储存消息的变量

Declare @Msg varchar(8000)

--将"操作／表名／日期时间／插入字段"赋与消息

set @Msg = ''Inserted | tablefortrigger | '' + convert(varchar(20), getdate()) + '' | ''
+(select convert(varchar(5), track)
+ '', '' + lastname + '', '' + firstname
from inserted)

--产生错误发送给事件查看器。

raiserror( 50005, 10, 1, @Msg)

运行以下语句对触发器进行测试，然后查看事件日志：

Insert into tablefortrigger(lastname, firstname)
Values(''Doe'', ''John'')

如果你打开事件日志，你应该看到以下消息：

既然我们已经有办法写入事件日志了，那么让我们修改一下触发器，将数据写到一个文本文件中。这次改动还须添加另一个变量@CmdString，以及使用扩展储存过程xp_cmdshell。

因为我们要写入文件系统，安全权限开始有影响了。所以，执行插入操作的用户必须具备该文本文件的读写权限。因此，设计一个C／S结构的应用程序供多用户运行，或许不是一个可行的解决方案。更合理的方案是，设计一个三层应用程序，由你的中间层组件对单用户数据库进行调用。在后一个方案中，对那个文本文件的权限管理其实比管理一个用户还容易。

Alter trigger TestTrigger on
tablefortrigger
for insert
as
Declare @Msg varchar(1000)

--储存将由xp_cmdshell执行的命令

Declare @CmdString varchar (2000)
set @_msg = '' insert | tablefortrigger | '' + convert ( varchar ( 20 ) , getdate ( ) ) + '' | '' + ( select convert ( varchar ( 5 ) , track ) + '' , '' + lastname + '' , '' + firstname from insert ) -
[99%]set @Msg = ''Inserted | tablefortrigger | '' + convert(varchar(20), getdate()) + '' | '' +(select convert(varchar(5), track) + '', '' + lastname + '', '' + firstname from inserted)

--产生错误发送给事件查看器。

raiserror( 50005, 10, 1, @Msg)
set @CmdString = ''echo '' + @Msg + '' >> C:\logtest.log''

凌众科技专业提供服务器租用、服务器托管、企业邮局、虚拟主机等服务，公司网站：http://www.lingzhong.cn 为了给广大客户了解更多的技术信息，本技术文章收集来源于网络,凌众科技尊重文章作者的版权，如果有涉及你的版权有必要删除你的文章，请和我们联系。以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢!