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

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介:

作为一名数据库管理员,你尽力以各部门熟知的不同格式,向各部门提供它们所需要的数据。你通常将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')

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


(图1)

既然我们已经有办法写入事件日志了,那么让我们修改一下触发器,将数据写到一个文本文件中。这次改动还须添加另一个变量@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' 
--写到文本文件
exec master.dbo.xp_cmdshell @CmdString

让我们对它进行测试,先运行前面的插入语句,然后打开C:\logtest.log文件查看结果:

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

问题解决了,对不对?哦,还没完全解决。发生多次重复插入的事件是什么原因?在这个例子中,你必须分别地处理每条记录。为了达到这个目的,我们必须用一个会带来麻烦的游标来访问"隐蔽面"。在执行以前,我必须预先给予警告。你应当了解的是,当这个应用程序进行大规模地记录插入、更新或删除时要当心,因为它可能会耗费大量的内存。

像你从下面看到的一样,这次我们在前面那个例子的基础上稍加调整,引入了一个游标,对该插入表的全部记录进行循环读取。每条记录分别插入一条线条,将各个事件区分开来。 

ALTER trigger TestTrigger on tablefortrigger 
for insert 
as 
Declare @Msg varchar(1000) 
Declare @CmdString varchar (1000) 
Declare GetinsertedCursor cursor for 
Select 'Inserted | tablefortrigger | ' + convert(varchar(20), getdate()) + ' | ' 
+ convert(varchar(5), track) 
+ ', ' + lastname + ', ' + firstname 
from inserted 

open GetinsertedCursor 
Fetch Next from GetinsertedCursor 
into @Msg 

while @@fetch_status = 0 
Begin 
raiserror( 50005, 10, 1, @Msg) 
Fetch Next from GetinsertedCursor 
into @Msg 
set @CmdString = 'echo ' + @Msg + ' >> C:\logtest.log' 
exec master.dbo.xp_cmdshell @CmdString 
End 
close Getinsertedcursor 
deallocate GetInsertedCursor

现在让我们执行重复多次插入测试:

Insert into tablefortrigger(lastname, firstname) 
Select lastname, firstname from employees

结论

在继续完成之前,有些人认为必须考虑性能与安全问题。你将看到写入文本文件的开销,而对于一个每分钟处理5000项事务的数据库来说,这样大的开销也许不可接受。由于xp_cmdshell是在SQL外操作的,写入到文件的错误不会回滚事务。倘若入侵者使用一个隐蔽的途径来改变你的数据,这个事件不会被登记到那个文本文件中。不过事件日志将记录该次DML改动。作为一次最好的实践,各事件的编号应该被用于对照日志文件的各行记录,以便发现所有的差异。

有很多种方法可以达到本文目标,上述脚本也可以有许多的变化。我希望你能接受这个脚本,然后作出改进并提出建议,使它更有效率。

专注于企业信息化,最近对股票数据分析较为感兴趣,可免费分享股票个股主力资金实时变化趋势分析工具,股票交流QQ群:457394862

本文转自沧海-重庆博客园博客,原文链接:http://www.cnblogs.com/omygod/archive/2006/11/24/570635.html,如需转载请自行联系原作者
相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
目录
相关文章
|
2月前
|
存储 缓存 Apache
StarRocks+Paimon 落地阿里日志采集:万亿级实时数据秒级查询
本文介绍了阿里集团A+流量分析平台的日志查询优化方案,针对万亿级日志数据的写入与查询挑战,提出基于Flink、Paimon和StarRocks的技术架构。通过Paimon存储日志数据,结合StarRocks高效计算能力,实现秒级查询性能。具体包括分桶表设计、数据缓存优化及文件大小控制等措施,解决高并发、大数据量下的查询效率问题。最终,日志查询耗时从分钟级降至秒级,显著提升业务响应速度,并为未来更低存储成本、更高性能及更多业务场景覆盖奠定基础。
|
3月前
|
存储 缓存 数据库
数据库数据删除策略:硬删除vs软删除的最佳实践指南
在项目开发中,“删除”操作常见但方式多样,主要分为硬删除与软删除。硬删除直接从数据库移除数据,操作简单、高效,但不可恢复;适用于临时或敏感数据。软删除通过标记字段保留数据,支持恢复和审计,但增加查询复杂度与数据量;适合需追踪历史或可恢复的场景。两者各有优劣,实际开发中常结合使用以满足不同需求。
212 4
|
3月前
|
SQL 监控 数据挖掘
SLS 重磅升级:超大规模数据实现完全精确分析
SLS 全新推出的「SQL 完全精确」模式,通过“限”与“换”的策略切换,在快速分析与精确计算之间实现平衡,满足用户对于超大数据规模分析结果精确的刚性需求。标志着其在超大规模日志数据分析领域再次迈出了重要的一步。
388 117
|
4月前
|
存储 缓存 监控
【YashanDB数据库】数据库运行正常,日志出现大量错误metadata changed
数据库运行正常,日志出现大量错误metadata changed
|
1月前
|
存储 SQL Java
数据存储使用文件还是数据库,哪个更合适?
数据库和文件系统各有优劣:数据库读写性能较低、结构 rigid,但具备计算能力和数据一致性保障;文件系统灵活易管理、读写高效,但缺乏计算能力且无法保证一致性。针对仅需高效存储与灵活管理的场景,文件系统更优,但其计算短板可通过开源工具 SPL(Structured Process Language)弥补。SPL 提供独立计算语法及高性能文件格式(如集文件、组表),支持复杂计算与多源混合查询,甚至可替代数据仓库。此外,SPL 易集成、支持热切换,大幅提升开发运维效率,是后数据库时代文件存储的理想补充方案。
|
6月前
|
数据采集 数据库 Python
有哪些方法可以验证用户输入数据的格式是否符合数据库的要求?
有哪些方法可以验证用户输入数据的格式是否符合数据库的要求?
299 75
|
4月前
|
数据库 Python
【YashanDB知识库】python驱动查询gbk字符集崖山数据库CLOB字段,数据被驱动截断
【YashanDB知识库】python驱动查询gbk字符集崖山数据库CLOB字段,数据被驱动截断
|
5月前
|
SQL
【YashanDB 知识库】通过触发器复制 varchar(4000 char) 列的数据导致乱码
**问题简介:** 客户在使用触发器将表 A 中 varchar(4000 char) 列的数据复制到表 B 时,发现表 B 中的数据出现乱码且与表 A 不一致。此问题影响所有 YashanDB 版本,原因是 YashanDB 在处理触发器场景下的大长度 varchar 数据复制时存在机制错误。为避免该问题,建议将列长度修改为 varchar(2000 char) 或更小。数据正确性无法保证,需谨慎处理。 **验证方法:** 可通过创建表 A 和 B 及相应触发器进行测试。
【YashanDB 知识库】通过触发器复制 varchar(4000 char) 列的数据导致乱码
|
3月前
|
人工智能 关系型数据库 分布式数据库
让数据与AI贴得更近,阿里云瑶池数据库系列产品焕新升级
4月9日阿里云AI势能大会上,阿里云瑶池数据库发布重磅新品及一系列产品能力升级。「推理加速服务」Tair KVCache全新上线,实现KVCache动态分层存储,显著提高内存资源利用率,为大模型推理降本提速。

热门文章

最新文章