目录
介绍
背景——审计表
背景——数据版本控制
让我们一石二鸟，并使用审计表也用于版本化数据
使事情变得清晰的视图示例
审计表
审计触发器
上下文信息
从C# EF6传递上下文
最终评论和一些高级技术
源代码
- 下载源代码 - 50.9 KB
有很多关于如何为SQL Server创建和维护审计表的文章，但其中大多数使用的数据库设计不允许您将其用作数据版本控制表。本文介绍了一种简单但功能强大且同构的设计，它使您可以轻松维护既可用于审计目的又可用于数据版本控制的表，以便您可以只保留主表中的活动数据，并将审计表用于历史和报告查询。
背景——审计表审计表用于跟踪针对一个或多个特定表的事务，有时甚至可以用于审计SELECT表上的只读查询(S)（但这不是本文的主题）。
SQL Server具有开箱即用的审计功能和其他一些替代方案，但是您可能更喜欢自定义解决方案，这样您可以更好地控制和更好地了解审计表。
对此问题的一种流行的基于触发器的解决方案是使用SQL脚本生成审计表（也称为影子表）和触发器。该解决方案为每个操作（Insert、Update、Delete）创建一条记录，显然添加了一些列，例如操作日期和进行操作的用户。这种设计的问题是很难找到给定时间特定记录的状态——显然，您可以使用TOP 1和ORDER BY在任何时间点查找单个记录的状态，但是当您必须连接版本化表，甚至查找在给定时间存在的一组记录时，这变得很困难。所以这不是版本化数据的好解决方案。
另一个问题是，除非您的应用程序使用Windows身份验证（或者您仍然处于90年代，当时客户端——服务器应用程序的每个用户都具有到数据库的专用数据库连接是很常见的），否则记录进行操作的数据库用户是无用的——您可能想知道哪个应用程序用户进行了操作。
还有其他解决方案可能（也可能不会）通过仅跟踪修改后的列来节省一些空间，但它们也面临着我之前提到的相同问题。
就我个人而言，我认为浪费一些磁盘空间来支持一些给我更好的性能并使日常开发更容易的东西要好得多，因为开发人员的时间通常比磁盘空间昂贵得多。
背景——数据版本控制当人们考虑版本化数据时，他们通常会想到将版本化记录与主表（活动记录所在的位置）存储在同一个表中。请不要。这会从根本上增加查询的复杂性，以确保使用每条记录的最新版本，并且不会因为过去的记录而复制结果。这很容易出错。它还会损害数据库的性能。数据库设计中最常见的版本控制错误是将过去的价格与当前价格保存在同一个表中。存储过去版本数据的最佳位置是在单独的表中。
同样，出于同样的原因，使用软删除（那个著名的IsDeleted标志）也是一个坏主意。放置历史数据的正确位置是在单独的报告数据库中，而不是在您的事务应用程序中。如果您了解此设计并遵循此规则，请注意您认为已删除的记录：应删除（并可能移入您的审计/历史表）的记录是不应该存在且被错误创建的记录，或记录代表当前实体的过去状态或类似场景。重复的客户是应该删除的一个很好的例子（特别是因为它会迫使您将相关实体合并到正确的记录中或级联删除它们）。一批计算错误（尚未用于您的每月余额或税款）的会计分录（帖子）应该被删除（并且应该被记录到您的审计表中）。
一般来说，如果一个实体可以起死回生（像student），它不应该被删除（和标记为非活动是完全正确的，应该不是一个软删除混淆），但如果实体是不对的，并可能以某种方式重新创建（如会计分录）或已经创建（如版本记录、某些产品的当前价格或重复的客户），那么它应该被删除（而不是软删除，这会给你带来的伤害远大于好处）。一个很好的气味，你不正确使用软删除是当你的应用程序按钮显示为“Delete（删除）" 并且您并没有真正删除。如果您只是停用记录，则按钮可能应该反映这一点。
总之，您的事务表应该只保留活动数据——而不是删除的记录，而不是过去的修订。不要偷懒：为历史数据创建新表（和新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)、插入的日期以及插入的用户。
米老鼠更新了产品（改变了单价）。
当某些记录更新时，审计表将创建一个新记录，该记录将跟踪记录的新状态，并且还应标记先前的修订不再有效。右上角突出显示的字段是新修订的跟踪信息，这与用于更新先前修订的生命周期结束的跟踪信息相同（左下突出显示）。
请注意，新修订版中使用的日期和交易与用于标记过去修订版生命周期结束的日期和交易完全相同——这为您提供了一种简单而优雅的方式将先前状态链接到新状态，并且使用完全相同的日期时间对于拥有连续的时间段很重要。另请注意，EndOperation修订版1的标记为“U”，因为该修订版并未删除，而是更新为新状态。
唐老鸭删除了该产品。
当某些记录被删除时，不会创建新的修订，但是必须标记以前的活动修订以通知它不再有效。突出显示的字段是在先前版本中更新的字段，并显示删除的用户和删除日期。
查询将像这样简单：
-- 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'请注意，其中一些查询假设您正在使用代理键，这保证在正常情况下，每个键只有一次插入和最多一次删除。
毕竟，使用代理键几乎总是（如果不是总是）一个不错的选择。
审计表这是审计表的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 上下文信息来传递有关当前登录用户的信息，但后来我决定为此使用临时表，以避免二进制序列化的复杂性。
这是我将信息传递给触发器的方式：
CREATE PROCEDURE [dbo].[sp_SetContextInfo]
@UserID INT,
@Username varchar(128) = NULL
AS
BEGIN
CREATE TABLE #session ([Username] varchar(128), [UserID] int NOT NULL)
INSERT INTO #session VALUES (@Username, @UserID)
END 这是我从触发器接收信息的方式：
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#和Entity Framework 6），以便可以将每个更改跟踪到正确的用户：
namespace NorthwindAudit
{
partial class NorthwindAuditDB
{
///
/// Currently logged user that is using the connection. For auditing purposes.
///
public string Username { get; set; }
///
/// Currently logged user that is using the connection. For auditing purposes.
///
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项被插入、更新，然后被删除。（有2行，但我将其设为垂直，因此屏幕截图适合文章。）：
如果我手动更新（在SQL Management Studio上）表，它将跟踪SQL用户、主机名和IP：
- 您可以在单独的文件组上创建AUDIT表（因为增长？）。但是，这将使您可以自由地单独恢复审计表，我认为这是危险的，而不是有用的。
- 我在不同的架构下创建了AUDIT表，因此触发器必须在对该架构具有权限的某个用户上运行“WITH EXECUTE AS”。我的应用程序的常规数据库用户无法访问审计表。
- 您可以使用NULL，而不是"infinite"，以便我的查询可以使用BETWEEN而不是检查null或使用COALESCE。
- 您不能在“插入”表中使用text、ntext或image列。您可以通过在真实表中查找数据来避免这种情况，因为触发器在insert/update发生之后运行。只需将插入的与真实表连接起来，并引用真实表上的那些列。
- 对于更新，我不会检查是否真的发生了变化。我真的不需要它，因为实体框架仅在某些内容真正发生变化时才发送更新。如果您需要检查修改（性能损失很小），您还可以将插入的表与真实表连接起来，并且仅在修改某些内容时插入。
- 对于更新，您还可以通过在没有修改该列的每个UPDATE列上保留NULL来仅跟踪更改的列，但是对于可为空的列，您将无法判断它何时是NULL或何时是“未修改”。即使对于不可为空的列，我仍然认为它不值得——我更喜欢有一个简单和同质的设计，因为审计表反映了与我的事务表完全相同的状态。如果我需要为更改的内容制作一个人类可读的日志，那是另一个算法的责任，而不是表格的责任。
- 我正在跟踪每一列的修改。同样，与节省一些磁盘空间相比，我更喜欢拥有一个简单且同类的解决方案。
下载NorthwindAudit.zip，您将在其中找到用于审计表和触发器（AuditTables.tt和AuditTriggers.tt）的T4生成器，以及EF Code-First类Northwind和文章中的示例代码。
https://www.codeproject.com/Articles/1112660/Audit-Tables-and-Data-Versioning-on-SQL-Server
