在之前一篇介紹CDC的文章中，我說Audit Trail（或者Audit Log）是大部分企業級應用不可以或缺的功能。本篇給你一個完整的Audit Trail解決方案，不僅可以記錄每一筆業務操作的信息（比如操作時間、操作者等），并且可以追蹤每一筆業務引起的說有數據的改變（如果需要）。

　　在數據庫中，我們通過如右圖所示的具有主子關系的兩個表存儲AuditLog相關信息。我們將"事務"作為我們進行追蹤的單位，不過這里的講的"事務"更多地指業務處理事務的概念。每一個被追蹤的事務在AuditLog表具有一條匹配的記錄，該記錄表示該事務的基本信息：UserName（操作者）、AuditTime（操作時間）、Activity（可以看成是對事物的命名）和Description（事務補充性的描述）。主鍵TransactionId唯一標識一個事務。

　　子表AuditLogData記錄事務詳細的信息，即事務所引起的數據變化。一個完整的業務邏輯往往涉及到對多個數據表、多條記錄的操作。而AuditLogData每一條記錄表示某個事務針對某個單一數據表所帶來的數據變化，而SourceTable字段表示源表的名稱。而DataChange字段以XML的形式表示數據的改變，它具有如下的格式。

二、數據變化的表示

　　數據操作類型無外乎添加、更新和刪除，我們通過不同的XML結構表示不同操作引起的數據改變。具體來說，對于添加操作，我們需要記錄下插入的記錄；對于刪除操作，需要記錄下原來的記錄；而對于數據更新，則需要同時記錄下更新先后的記錄。

　　舉個例子，假設我們具有一個Users表，它具有三個基本字段：Id、Name和Birthday。下面的XML分別表示添加、刪除和更新操作后我們需要記錄下的數據變化。

添加：

　　<?xml version="1.0" encoding="utf-8" ?>
<cdc operation="insert">
<current>
<Id type="VARCHAR(50)">001</Id>
<Name type="NVARCHAR(50)">Foo</Name>
<BirthDay type="DATE">1981-08-24</BirthDay>
</current>
</cdc>

刪除：

　　 <?xml version="1.0" encoding="utf-8" ?>
<cdc operation="delete">
<original>
<Id type="VARCHAR(50)">001</Id>
<Name type="NVARCHAR(50)">Foo</Name>
<BirthDay type="DATE">1981-08-24</BirthDay>
</original>
</cdc>

更新：

　　 <?xml version="1.0" encoding="utf-8" ?>
<cdc operation="update">
<original>
<Id type="VARCHAR(50)">001</Id>
<Name type="NVARCHAR(50)">Foo</Name>
<BirthDay type="DATE">1981-08-24</BirthDay>
</original>
<current>
<Id type="VARCHAR(50)">001</Id>
<Name type="NVARCHAR(50)">Bar</Name>
<BirthDay type="DATE">1982-07-10</BirthDay>
</current>
</cdc>

　　當然，你也可以根據需要自定義XML的結構。

三、AuditLog基本信息的寫入

　　我們現在我們的目標就是如何將追蹤到的基于一個事務相關的信息寫入到上面我們創建的兩個表中。主表AuditLog的信息是很容易被寫入的，比如你可以定義像下面一樣的一個AuditLogger類。

　　 public class AuditLogger
{
public void Write(string activity)
{ }

public void Write(string activity, string description)
{ }
}

　　AuditLogger的Write方法進行傳入了Activity和Description，而沒有TransactionId、UserName和AuditTime。其中AuditTime自然是當前時間，而UserName應該是登錄系統的用戶。而對于TransactionId，我們應該采用上下文的方式來獲取，具體原因會在下面談到。如果你直接使用System.Transactions事務實現我們進行追蹤的"事務"，你可以直接使用當前事務（Transaction.Current）的DistributedIdentifier或者LocalIdentifier。

　　 var transactionId = Transaction.Current.TransactionInformation.DistributedIdentifier;
//Or
var transactionId = Transaction.Current.TransactionInformation.LocalIdentifier;

　　基于AuditLog表的事務基本信息的日志好解決，那么我們如何將事務引起的事務變化記錄到AuditLogData表中呢？這樣的工作我們完全實現在SQL Server中。

四、通過SQLCDC追蹤源表數據變化

　　《追蹤記錄每筆業務操作數據改變的利器——SQLCDC》介紹了一種有效記錄基于某個數據表數據變化的方式：SQLCDC，在這里我們直接利用它來記錄AuditLog的詳細信息。當我們為某個表（比如Users）開啟了CDC特性之后，SQL Server會為之創建一個相應的CT表（Users_CT），在默認的情況下Users_CT包含與Users表的所有字段。如果你不希望CDC追蹤所有的字段，你可以顯式地設定具體的字段。

　　AuditLogData表中有一個字段TransactionId表示記錄屬于哪個具體的事務，為了讓CDC可以記錄下正確TransactionId，需要在每一個被追蹤的表中添加這么一個額外的字段。這個應該不是什么問題，比如我們的每個表中都具有6個系統字段：TransactionId、VersionNo、CreatedBy、CreatedTime、LatestUpdatedBy和LatestUpdatedTime。

　　由于每個數據表都具有了一個TransactionId字段，那么在進行數據提交的時候，需要將當前事務的ID為之賦值，這就是為什么我推薦采用上下文的方式來獲取當前TransactionId的原因。但是，還有一個問題沒有解決&mdash;—數據刪除操作的TransactionId如何被記錄下來呢？

五、刪除操作的TransactionId如何被記錄？

　　由于代表當前事務的TransactionId最終會通過Insert或者Update SQL語句寫入數據表，但是對于刪除操作呢？由于我們直接調用Delete語句將相應的數據操作，表示當前刪除操作所在的事務是無法被寫入的，最終CDC記錄下來的數據是無法反映出刪除的記錄隸屬于哪個事務。

　　由于最終對數據庫操作都是通過SQL提交的，或者是存儲過程，或者是SQL文本。為了解決這個問題，我們只需要改變我們的SQL腳本，在Delete執行之前執行Update語句寫入新的TransactionId。

　　也就是說，對于一個刪除操作，實際上是先做Update，最后做Delete。在這種情況下，CDC會為你記錄下三條記錄，前兩條是為Update記錄的，最后一條是為Delete記錄的。為了區分CDC追蹤的記錄是正常的Update還是為了Delete而進行的Update，我們可以做一些標記。比如你可以在TransactionId的值之前添加一個前綴，表示Update操作是為Delete而作的。

六、通過SQL Job轉儲AuditLog詳細信息

　　CDC僅僅會將基于某個表的數據改變記錄到基于該表的CT表中，最終我們需要將這些CT表中的數據轉存到我們指定的AuditLogData表中，這個工作可以通過SQLJob來實現。你自行創建一個SQL Job實現從若干CT表到AuditLogData的數據轉存，并根據你的需要（主要是實時性的需要）配制Job執行的時間或者間隔下圖揭示了AuditLog詳細信息是如何一步步地被記錄的。

七、代碼生成的應用

　　在這個解決方案中，我們需要一個不可或缺的東西：代碼生成器。它用于自動生成如下的SQL腳本：為某個表開啟CDC特性并指定追蹤字段的T-SQL腳本，和進行AuditLog詳細信息轉存（叢CT表到AuditLogData表）的SQL Job腳本。關于代碼生成，可以參考《與VS集成的若干種代碼生成解決方案》