使用触发器实现审计跟踪

Shivprasad koirala

4.33/5 (17投票s)

2009年4月27日

CPOL

5分钟阅读

98896

1780

使用触发器实现审计跟踪。

下载源代码 - 1.04 MB

引言和目标

实现审计追踪的方法有很多，在我之前的一篇文章中，我讨论了如何使用原型模式实现审计追踪。在本节中，我们将讨论如何使用触发器实现审计追踪。我们将以一个简单的客户表为例，创建一个简单的审计表，然后编写一个通用触发器，通过它我们可以在审计表中审计任何类型的表结构数据。

您可以下载我的400 .NET FAQ 书，其中包含了您在 .NET 世界中所需的一切。
数据为 XML 格式。

这就是我们在本教程结束时将实现的目标——一个简单的审计表，其中包含 XML 格式的审计数据.

基础知识 – inserted 和 deleted 逻辑表

基于触发器的审计追踪基于两个逻辑表：“inserted”和“deleted”。因此，我们首先将了解这些表的基础知识，然后查看实际实现。

我们将考虑 3 种不同的场景，即插入、更新和删除，我们将看到这三种场景如何影响“inserted”和“deleted”表中的数据。

因此，当对表触发插入 SQL 时，它会将此新记录插入到“inserted”逻辑表中。

图：插入操作。

当对表触发更新 SQL 时，它会将更新后的新记录插入到“inserted”逻辑表中。旧记录，即更新前的记录，将输入到 deleted 表中。

图：更新和删除操作。

对表触发的任何删除都会将删除的记录插入到“deleted”逻辑表中。

图：删除操作。

我们只关注 deleted 表

仅当记录更新或删除时才维护审计追踪。换句话说，我们只关心 deleted 表。“deleted”逻辑表包含更新或删除之前的记录。

通用审计表

审计表将具有通用结构以存储任何类型的审计值。有三个字段，其中“AuditValue”是最重要的字段。此字段将以 XML 格式存储旧值。我们使用 XML 格式，以便我们可以适应任何通用表更改。

字段名	描述
`ID`	一个递增的标识值。
`AuditValue`	此字段包含 XML 格式的更新值。例如，`<Table1 Field1="123"/>`表示`table1`的`field1`值已更改，更新前的旧值为“`123`”。
`TableName`	此字段包含值已更改的表名。

简单的客户表

作为示例，我们使用了一个简单的客户表，并将在其上实现审计追踪。这是一个简单的客户表，具有以下三个字段及其描述：

字段名	描述
CustomerId	一个递增的标识值
客户名称	客户名称
客户地址	客户地址

触发器 + deleted 表 + FOR XML

因此，我们将在客户表上创建一个简单的触发器，该触发器将查询 deleted 表并将更新后的值存储在审计表中。

第一步是为更新和删除事件创建触发器。

CREATE TRIGGER Trigger2
ON dbo.Customer
for UPDATE,DELETE

我们需要做的第二件事是检查这是更新还是删除。如果您还记得 inserted 和 deleted 的基础知识，您会记得更新的数据同时出现在 inserted 和 deleted 表中。但删除的数据只进入 deleted 逻辑表。因此，如果数据存在于“inserted”表中，则意味着它是一个更新事件，否则它是一个删除事件。下面是相关的代码片段：

IF (SELECT COUNT(*) FROM inserted) > 0 
begin
-- This is a update
end
else
-- This is a delete
end

如果记录被删除，我们可以在 deleted 表中找到它。我们可以使用“FOR XML”关键字将行转换为 XML 格式。因此，我们只需查询 deleted 表数据并使用“FOR XML”将其转换为 XML 格式。最后，我们可以将此 XML 数据插入到审计表中。

set @OldMessage = (SELECT CustomerName ,CustomerAddress
from deleted 
FOR XML AUTO)

insert into audit(AuditValue,TableName) values (@OldMessage,'Customer')

如果记录已更新，我们可以与客户表进行内连接，并从 deleted 逻辑表中获取更新的记录。我们还使用了“update”函数来检查列是否已更新。

set @OldMessage = (SELECT (case
when update(CustomerName)
then deleted.CustomerName
end) as CustomerName,
(case when update(CustomerAddress)
then deleted.CustomerAddress
end) as CustomerAddress from deleted 
inner join Customer
on deleted.CustomerId=Customer.CustomerId 
FOR XML AUTO)

将它们组合在一起形成最终的触发器

ALTER TRIGGER Trigger2
ON dbo.Customer
for UPDATE,DELETE
AS
Declare @OldMessage varchar(200)

IF (SELECT COUNT(*) FROM inserted) > 0 
begin
set @OldMessage = (SELECT 
(case
when update(CustomerName)
then deleted.CustomerName
end) as CustomerName,
(case when update(CustomerAddress)
then deleted.CustomerAddress
end) as CustomerAddress from deleted 
inner join Customer
on deleted.CustomerId=Customer.CustomerId 
FOR XML AUTO)
end
else
begin
set @OldMessage = (SELECT CustomerName ,CustomerAddress
from deleted 
FOR XML AUTO)
end

insert into audit(AuditValue,TableName) values (@OldMessage,'Customer')

预备，开始行动

我随机更新了客户表中的数据，您可以看到审计追踪如何以 XML 格式记录在审计表中。

显示审计追踪

如果要反向显示数据，可以使用“OPENXML”关键字。

DECLARE @idoc int
DECLARE @doc varchar(1000)

select @doc=AuditValue from audit where id=60

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT * FROM OPENXML (@idoc, '/deleted',0) WITH _
	(CustomerName varchar(50), CustomerAddress varchar(50))

EXEC sp_xml_removedocument @idoc

下面是审计数据如何以列和行格式显示回来的简单示例。

此实现的问题

由于 XML，大小可能非常大。某种压缩机制可能会非常有帮助。
可能会出现性能问题，因为需要额外触发器。但根据我们的压力测试，它非常小。对于高事务表，在实际项目中实施之前进行负载测试至关重要。
您需要为您要审计的每个表创建触发器。
您仍然需要进行某种手动过程来进行审计，即创建触发器、填写表名等。换句话说，它不是一个通用解决方案。

关于源代码下载

我们已附上完整的 MDF 文件，其中包含客户表、审计表和我们上面讨论的触发器。我们已附上 MDF 和 LDF。请注意，数据库是使用 SQL Express 版本创建的。
要下载源代码，请点击此处。

使用触发器实现审计跟踪

目录

关于源代码下载