SQL Server 上的审计表和数据版本控制
4.99/5 (22投票s)
一种简单的结构，用于使用审计触发器和实体框架在同一组表中保留 SQL Server 审计表和版本化记录
引言
有许多关于如何创建和维护 SQL Server 审计表的文章，但大多数文章使用的数据库设计不允许您将其用作数据版本控制表。本文描述了一种简单而强大且同类的设计，可让您轻松维护既可用于审计目的也可用于数据版本控制的表，以便您可以在主表中仅保留活动数据，并使用审计表进行历史和报告查询。
背景 - 审计表
审计表 用于跟踪对特定表或表的事务，有时也用于审计只读查询（SELECTS），但这不是本文的主题。
SQL Server 具有开箱即用的 审计功能，以及其他一些 替代方案，但您可能更喜欢 **自定义解决方案**，以便您可以更好地控制和理解审计表。
一种流行的基于触发器的解决方案是 本文 中描述的（包含用于生成 *审计表*（也称为 *影子表*）和 *触发器* 的 SQL 脚本）。此解决方案（来自文章）为每次操作（Insert、Update、Delete）创建一个记录，并添加一些附加列，如操作日期和执行操作的用户。该设计的问题在于 **很难在给定时间点找到特定记录的状态** - 当然，您可以使用 TOP 1 和 ORDER BY 来查找任何时间点的单个记录状态，但这在您必须联接版本化的表时变得困难，甚至难以查找当时存在的记录集。因此，它 **不是版本化数据的良好解决方案**。
另一个问题是，除非您的应用程序使用 Windows 身份验证（或者您仍然停留在 90 年代，那时 客户端-服务器应用程序的每个用户 都有专用的数据库连接），否则记录执行操作的 **数据库用户** 是无用的 - 您可能想知道 **应用程序用户** 进行了什么操作。
还有 其他 解决方案 可能（或不）通过仅跟踪修改的列来节省一些空间，但它们也面临我之前提到的相同问题。
我个人认为，**浪费一些磁盘空间** 以换取 **更好的性能** 和 **更轻松的日常开发** 要好得多，因为开发人员的时间通常比磁盘空间昂贵得多。
背景 - 数据版本控制
当人们想到版本化数据时，他们通常会想到 将版本化记录存储 在 **与您的主表（您的活动记录所在之处）相同的表中**。**请 不要 这样做**。这会极大地增加 **查询的复杂性**，以确保使用每个记录的最新版本，并且不会因为过去的记录而重复结果。这 **非常容易出错**。它还会 **损害数据库的性能**。数据库版本控制设计中最常见的错误是 **将过去的价格** 与当前价格保留在同一表中。
存储过去数据版本的最佳位置是单独的表中.
同样，使用 *软删除*（那个著名的 IsDeleted 标志）也是一个 **糟糕的主意**，原因相同。放置 **历史数据** 的正确位置是 **单独的报告数据库**，而不是事务性应用程序内部。如果您理解此设计并遵循此规则，请注意您如何定义已删除记录：应该删除（并可能移至审计/历史表）的是不应该存在且被错误创建的记录，或代表当前实体的过去状态的记录，或类似场景。重复的客户是一个很好的例子，应该删除（特别是因为它会迫使您将相关实体合并到正确的记录中，或级联删除它们）。一组不正确计算的会计分录（未用于您的月度余额或税款）应该被删除（并且可能记录在您的审计表中）。未激活的学生（因为他没有报名任何课程）不应该被删除。
总的来说，如果一个实体可以 **重获生命**（如 student），它就不应该被删除（标记为不活跃是完全正确的，并且 **不应** 与软删除混淆），但如果实体 **只是错误的** 并且可以以某种方式重新创建（如会计分录）或已经创建（如版本化记录，如某种产品的当前价格，或重复的客户），那么它 **应该被删除**（而不是软删除，这只会造成更多麻烦）。如果您错误地使用软删除，一个好的 **迹象** 是，您的应用程序按钮显示“**删除**”，而您实际上并没有删除。如果您只是使记录失效，那么按钮可能应该反映这一点。
总之，您的 **事务表应该只保留活动数据 - 不包含已删除记录和过去修订**。不要懒惰：为历史数据创建新表（和新 CRUD 操作）- 这只需要几分钟，但以后会为您节省无数小时，所以这是一项好的投资。
一石二鸟，将审计表也用于版本化数据
由于 **版本控制表** 和 **审计表** 有很多共同之处，我决定使用单一结构来处理两者。这促使我对 本文 的触发器/审计表进行了一些更改。
这是我提议的审计表设计
- 与其他解决方案一样，每个审计表都具有与被审计表相同的列，只是增加了一个新的标识主键。
- 为了跟踪执行操作的用户，我同时保留了 **ID**（
int）和 **用户名**（varchar）列。当我可以识别应用程序用户时，我拥有他的 ID 和他的姓名或登录名。当无法识别执行事务的用户时（当某些事情发生在应用程序之外时），我会跟踪使用的 **SQL 用户**、**主机名** 和 **IP**。 - 每个审计行都有两列，用于跟踪 **该记录修订何时开始存在**（可以是新记录，也可以是从先前状态修改而来），以及 **该记录修订何时停止存在**（可以是已删除的记录，也可以是修改为更新状态）。
AuditStartDate跟踪记录修订的开始时间，AuditEndDate跟踪该修订的结束时间。AuditStartUserID、AuditStartUserName、AuditEndUserID和AuditEndUserName分别是使记录进入该状态以及从中移除记录的用户 ID 和用户名。AuditStartOperation如果被跟踪的记录是新的（第一次修订），则为 **I**（INSERTED），如果被跟踪的记录已存在并被更新，则为 **U**（UPDATED）。AuditEndOperation如果被跟踪的记录因被删除而停止存在，则为 **D**（DELETED），如果被跟踪的记录只是更新为新状态，则为 **U**。AuditStartTransactionGUID和AuditEndTransactionGUID只是我用来知道哪些操作发生在同一事务中的唯一标识符，主要用于将记录的先前状态连接到下一个状态（稍后将详细介绍）。- 您可能已经注意到，我没有为每个记录的修订编号设置
RevisionID。这将迫使我引用审计表本身，甚至可能产生一些死锁。我只是决定我不需要它。我可以在需要时重新编号我的审计记录。
一个视觉示例让事情更清楚
Richard Drizin 创建了产品。
当创建某个记录时，审计表将创建一个记录，该记录将跟踪插入到被审计表中的所有信息（截图中的突出显示字段），并添加一些跟踪信息（未突出显示的字段），其中包含操作（Insert）、插入日期以及插入用户。
**Mickey Mouse 更新了产品**（更改了单价）。
当更新某个记录时，审计表将创建一个新记录，该记录将跟踪记录的新状态，并且还应该标记先前的修订不再有效。右上角的突出显示字段是新修订的跟踪信息，它与用于标记先前修订的生命周期结束的跟踪信息相同（左下角突出显示）。
请注意，新修订中使用的日期和事务与用于标记先前修订生命周期结束的日期和事务完全相同 - 这为您提供了一种轻松优雅的方式来链接先前状态与新状态，并且使用完全相同的日期时间对于拥有 **连续的时间段** 很重要。另请注意，修订 1 的 EndOperation 被标记为 **“U”**，因为该修订并未被删除，而是被更新为新状态。
Donald Duck 删除该产品。
当删除某个记录时，不会创建新修订，但必须标记先前活动的修订以告知其不再有效。突出显示的字段是先前修订中更新的字段，显示了删除用户和删除日期。
查询将像这样简单:
-- To find the ACTIVE version
SELECT * [FROM Audit_Products] WHERE GETDATE() BETWEEN AuditStartDate AND AuditEndDate
-- or
SELECT * [FROM Audit_Products] WHERE AuditEndDate='9999-12-31'
-- or
SELECT * [FROM Audit_Products] WHERE AuditEndOperation IS NULL
-- To find the version that existed at any given time
SELECT * [FROM Audit_Products] WHERE @SomeDate BETWEEN AuditStartDate AND AuditEndDate.
-- AuditEndOperation would indicate if that version is still active (NULL),
-- if it was DELETED ('D') or if it was somehow UPDATED ('U')
-- To find the first version
SELECT * [FROM Audit_Products] WHERE AuditStartOperation='I'
-- To find the last version (even if deleted)
SELECT * [FROM Audit_Products] WHERE AuditEndDate='9999-12-31' OR AuditEndOperation='D'
请注意，其中一些查询假设您使用的是 **代理键**（surrogate keys），这保证了在 **正常情况下**，每个键 **只会有一个插入，最多有一个删除**。
最终，使用代理键 **几乎总是**（如果不是总是）一个好选择。
审计表
这是 Northwind Orders 表的 **审计表**。它有一个标识主键（不是原始表的键），加上原始 Products 表的所有列，最后是所有 Audit 列。
本文的源代码包含一个 T4 模板，用于生成审计触发器。
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [audit].[Audit_dboProducts](
[Audit_dboProductsID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NOT NULL,
[CategoryID] [int] NULL,
[Discontinued] [bit] NOT NULL,
[ProductName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[QuantityPerUnit] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReorderLevel] [smallint] NULL,
[SupplierID] [int] NULL,
[UnitPrice] [money] NULL,
[UnitsInStock] [smallint] NULL,
[UnitsOnOrder] [smallint] NULL,
[AuditStartDate] [datetime] NOT NULL,
[AuditEndDate] [datetime] NOT NULL,
[AuditStartOperation] [char](1) COLLATE Latin1_General_CI_AS NOT NULL,
[AuditEndOperation] [char](1) COLLATE Latin1_General_CI_AS NULL,
[AuditStartUserID] [int] NOT NULL,
[AuditStartUsername] [varchar](128) COLLATE Latin1_General_CI_AS NOT NULL,
[AuditEndUserID] [int] NULL,
[AuditEndUsername] [varchar](128) COLLATE Latin1_General_CI_AS NULL,
[AuditStartTransactionGUID] [uniqueidentifier] NOT NULL,
[AuditEndTransactionGUID] [uniqueidentifier] NULL,
PRIMARY KEY CLUSTERED
(
[Audit_dboProductsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
审计触发器
这是同一表的 **审计触发器**。请注意，对于 INSERTS、UPDATES 和 DELETES，使用的是相同的触发器：“UPDATES”和“DELETES”会“关闭”先前现有修订的生命周期，而“INSERTS”和“UPDATES”也会为该记录创建新修订。
本文的源代码包含一个 **T4** 模板，用于生成审计触发器。
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trAuditProducts] ON [dbo].[Products]
WITH EXECUTE AS 'audituser'
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON -- Trigger cannot affect the "rows affected" counter,
-- or else it would break Entity Framework
-- Logged User
DECLARE @UserID INT
DECLARE @Username varchar(128)
DECLARE @Now datetime
DECLARE @TransactionGUID UNIQUEIDENTIFIER
EXEC [dbo].[sp_GetContextInfo] @UserID OUTPUT, @Username OUTPUT, @TransactionGUID OUTPUT
DECLARE @infinite DATETIME
SET @infinite = '9999-12-31'
-- InsertUpdate
DECLARE @Action varchar(1)
SET @Action = 'D'
-- Defining if it's an UPDATE (U), INSERT (I), or DELETE ('D')
IF (SELECT COUNT(*) FROM inserted) > 0 BEGIN
IF (SELECT COUNT(*) FROM deleted) > 0
SET @Action = 'U'
ELSE
SET @Action = 'I'
END
SET @Now = GETDATE()
-- Closing the lifetime of the current revisions (EndDate=infinite)
-- for records which were updated or deleted
IF (@Action='D' OR @Action='U')
UPDATE [audit].[Audit_dboProducts]
SET [AuditEndDate] = @Now,
[AuditEndUserID] = @UserID,
[AuditEndUsername] = @Username,
[AuditEndTransactionGUID] = @TransactionGUID,
[AuditEndOperation] = @Action
FROM [audit].[Audit_dboProducts] aud
INNER JOIN deleted tab
ON [tab].[ProductID] = [aud].[ProductID]
AND aud.[AuditEndDate] = @infinite
-- Creating new revisions for records which were inserted or updated
IF (@Action='I' OR @Action='U') BEGIN
INSERT INTO [audit].[Audit_dboProducts] ([ProductID],
[ProductName], [SupplierID], [CategoryID], [QuantityPerUnit],
[UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel],
[Discontinued], [AuditStartDate], [AuditEndDate], [AuditStartOperation],
[AuditStartUserID], [AuditStartUsername], [AuditStartTransactionGUID])
SELECT [inserted].[ProductID], [inserted].[ProductName],
[inserted].[SupplierID], [inserted].[CategoryID], [inserted].[QuantityPerUnit],
[inserted].[UnitPrice], [inserted].[UnitsInStock], [inserted].[UnitsOnOrder],
[inserted].[ReorderLevel], [inserted].[Discontinued],
@Now,
@infinite,
@Action,
@UserID,
@Username,
@TransactionGUID
FROM inserted
END
GO
上下文信息
要在您的表中跟踪哪个 **系统用户**（而不是数据库用户）执行了操作，您必须以某种方式将该信息从您的应用程序传递到您的数据库连接。起初，我使用 SQL Context Info 来传递当前登录用户的信息，但后来我决定使用临时表来传递信息，以避免二进制序列化的复杂性。
这是 **我如何将信息传递** 给我的触发器
IF OBJECT_ID('tempdb..#session') IS NOT NULL DROP TABLE #session;
CREATE TABLE #session (
[Username] varchar(128),
[UserID] int NOT NULL,
[TransactionGUID] UNIQUEIDENTIFIER NOT NULL);
INSERT INTO #session
([Username], [UserID], [TransactionGUID])
VALUES
(@Username, @UserID, NEWID());
这是 **我如何从我的触发器接收信息**
CREATE PROCEDURE [dbo].[sp_GetContextInfo]
@UserID INT OUTPUT,
@Username varchar(128) OUTPUT,
@TransactionGUID UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
SET @UserID = 0
SET @Username = NULL
SET @TransactionGUID = NEWID()
-- Get @Username and @UserID given by the application
IF OBJECT_ID('tempdb..#session') IS NOT NULL BEGIN
SELECT @Username = Username, @UserID = COALESCE(UserID, 0),
@TransactionGUID = COALESCE(TransactionGUID, NEWID())
FROM #session
END
IF (@Username IS NULL) -- if no application user was given, get sql user, hostname and ip
SELECT @Username = '[' + SYSTEM_USER + '] ' + RTRIM(CAST(hostname AS VARCHAR))
+ ' (' + RTRIM(CAST(CONNECTIONPROPERTY('client_net_address') AS VARCHAR)) + ')'
from master..sysprocesses where spid = @@spid
END
从 C# 实体框架 6 传递上下文
为了在您的表中跟踪审计表，哪个 **系统用户** 执行了操作，您必须以某种方式传递此信息，以便触发器可以使用它。以下是我如何将当前用户的信息传递给数据库连接（使用 **C# 和 Entity Framework 6**），以便每个更改都可以追溯到正确的用户。
namespace NorthwindAudit
{
partial class NorthwindAuditDB
{
/// <summary>
/// Currently logged user that is using the connection. For auditing purposes.
/// </summary>
public string Username { get; set; }
/// <summary>
/// Currently logged user that is using the connection. For auditing purposes.
/// </summary>
public int UserID { get; set; }
// modify your constructor to force developer to pass the username and userid.
public NorthwindAuditDB(string Username, int UserID) : this()
{
this.Username = Username;
this.UserID = UserID;
this.Configuration.LazyLoadingEnabled = true;
// you may want to disable this if you have some batch jobs
// that don't run on users context...
// but I like to enforce that caller always provide some user
if (this.UserID == 0 || this.Username == null)
throw new ArgumentNullException("You must provide the application user,
for auditing purposes");
this.Database.Connection.StateChange +=
new System.Data.StateChangeEventHandler(Connection_StateChange);
}
//pass the application user to the SQL when the connection opens
// (because the connection could already have been used by another DbContext)
void Connection_StateChange(object sender, System.Data.StateChangeEventArgs e)
{
// State changed to Open
if (e.CurrentState == ConnectionState.Open &&
e.OriginalState != ConnectionState.Open)
{
SetConnectionUser(this.UserID, this.Username);
}
}
void SetConnectionUser(int userID, string username)
{
// Create local temporary context table
var cmd = this.Database.Connection.CreateCommand();
cmd.CommandText = "IF OBJECT_ID('tempdb..#session')
IS NOT NULL DROP TABLE #session";
cmd.ExecuteNonQuery();
if (userID != 0 && username != null)
{
cmd.CommandText = "CREATE TABLE #session ([Username] varchar(128),
[UserID] int NOT NULL, [TransactionGUID] UNIQUEIDENTIFIER NOT NULL)";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO #session
([Username], [UserID], [TransactionGUID])
VALUES (@Username, @UserID, NEWID())";
cmd.Parameters.Add(new SqlParameter("@UserID", userID));
cmd.Parameters.Add(new SqlParameter("@Username", username ?? ""));
cmd.ExecuteNonQuery();
}
}
// This probably is not necessary, but I like to check that
// the session table matches the provided user.
// I haven't made stress testing for concurrency issues, so better safe than sorry.
public override int SaveChanges()
{
if (this.UserID == 0 || this.Username == null)
throw new ArgumentNullException("Necessário passar usuário da conexão,
para auditoria");
#region Just in case! Double checking that table #session was created
and that it matches the user for the context
bool wasClosed = false;
if (this.Database.Connection.State == ConnectionState.Closed)
{
this.Database.Connection.Open();
wasClosed = true;
}
var cmd = this.Database.Connection.CreateCommand();
cmd.CommandText = "EXEC [dbo].[sp_GetContextInfo] @UserID OUTPUT,
@Username OUTPUT, @TransactionGUID OUTPUT";
var parm1 = new SqlParameter("@UserID", SqlDbType.Int);
parm1.Direction = ParameterDirection.Output; cmd.Parameters.Add(parm1);
var parm2 = new SqlParameter("@Username", SqlDbType.VarChar, 128);
parm2.Direction = ParameterDirection.Output; cmd.Parameters.Add(parm2);
var parm3 = new SqlParameter("@TransactionGUID", SqlDbType.UniqueIdentifier);
parm3.Direction = ParameterDirection.Output; cmd.Parameters.Add(parm3);
//Error: ExecuteNonQuery requires an open and available Connection
//http://stackoverflow.com/questions/7201754/
executenonquery-requires-an-open-and-available-connection-the-connections-curr
cmd.ExecuteNonQuery();
if (wasClosed)
this.Database.Connection.Close();
if (parm1.Value == null || ((int)parm1.Value) == 0 ||
parm2.Value == null || string.IsNullOrEmpty((string)parm2.Value))
throw new ArgumentNullException
("You must provide a user for the connection, for auditing purposes");
if (((int)parm1.Value) != this.UserID || ((string)parm2.Value) != this.Username)
throw new ArgumentNullException("The user provided in #session table
does not match the user provided on the connection (DbContext)");
#endregion
return base.SaveChanges();
}
}
}
让我们使用 Northwind 数据库进行测试，并将当前用户传递给我们的 DbContext 的构造函数。
static void Main(string[] args)
{
// creating product, order and orderitem
var db = new NorthwindAuditDB("Richard Drizin", 27);
var product = new Product()
{
ProductName = "3/4 inches screw",
UnitPrice = 9.99m,
UnitsInStock = 23
};
var order = new Order()
{
CustomerID = "FRANK", // Customers PK is varchar
// in Northwind ... yeah I know
EmployeeID = 1,
OrderDate = DateTime.Now,
};
order.Order_Details.Add(new Order_Detail()
{
Product = product,
UnitPrice = product.UnitPrice.Value,
Quantity = 3,
});
db.Orders.Add(order);
db.SaveChanges();
// updating quantity of items
db = new NorthwindAuditDB("Mickey Mouse", 31);
var lastOrder = db.Orders
.Where(x => x.CustomerID == "FRANK")
.OrderByDescending(x=>x.OrderID).First();
lastOrder.Order_Details.First().Quantity++;
db.SaveChanges();
// deleting order and orderitem
db = new NorthwindAuditDB("Donald Duck", 33);
var lastOrder2 = db.Orders
.Where(x => x.CustomerID == "FRANK")
.OrderByDescending(x => x.OrderID).First();
db.Order_Detail.RemoveRange(lastOrder2.Order_Details);
db.Orders.Remove(lastOrder2);
db.SaveChanges();
}
结果
Product 被创建了一次，从未修改或删除。（我隐藏了 null 列，以便截图能适应文章）
Order 被插入，然后被删除（它是单行，但我将其垂直显示，以便截图能适应文章）。
Order item 被插入，更新，然后删除。（有 2 行，但我将其垂直显示，以便截图能适应文章。）
如果我在 SQL Management Studio 中手动更新表，它将跟踪 SQL 用户、主机名和 IP。
最终评论和一些高级技术
- 您可以将
AUDIT表创建在不同的文件组中（因为增长？）。但这将为您提供单独恢复审计表的自由，我认为这比有益更危险。 - 我将
AUDIT表创建在不同的模式下，因此触发器必须“WITH EXECUTE AS”在某个拥有该模式权限的用户上运行。我的应用程序的常规数据库用户无法访问审计表。 - 您可以使用
NULL而不是“无限”。我更愿意保留无限，以便我的查询可以使用BETWEEN而不是检查null或使用COALESCE。 - 您不能在“inserted”表中使用
text、ntext或 image 列。您可以通过查找真实表中的数据来规避这个问题，因为触发器在insert/update发生后运行。只需将 inserted 与真实表联接，并在真实表中引用那些列。 - 对于更新，我没有检查是否真的发生了更改。我真的不需要，因为 Entity Framework 只在真正更改时才发送更新。如果您需要检查修改（会带来一些性能损失），您也可以将 inserted 表与真实表联接，并且仅在修改时插入。
- 对于更新，您还可以 **只跟踪更改的列**，通过在未修改该列的每次
UPDATE上保留NULL，但这样对于可空列，您将无法区分它是NULL还是“未修改”。即使对于非可空列，我仍然不认为这值得 - 我更喜欢 **简单同类的设计**，这样审计表就反映了与我的事务表完全相同的状态。如果我需要一个人类可读的更改日志，那是 **另一个算法的职责**，而不是表的职责。 - 我正在跟踪所有列的修改。再说一次，我更喜欢一个简单同类的解决方案，而不是节省一些磁盘空间。
源代码
下载 NorthwindAudit.zip，其中您将找到用于审计表和触发器（AuditTables.tt 和 AuditTriggers.tt）的 T4 生成器，以及 Northwind 的 EF Code-First 类，以及本文中的示例代码。
历史
- 2016 年 7 月 16 日。第一个版本
这是我在 Code Project 上的第一篇文章. :-)