常被要求當資料表內的紀錄有被新增修改或刪除的時候，需要同步紀錄每次資料表變動的前後資料，這時候我們可以利用 Trigger 便可做到符合這個功能的需求，而且可以適用到大部份的使用情境，又因為 Trigger 本身也可以捕捉到資料庫的 DML 的事件，如 : INSERT、UPDATE 、DELETE ，我們也能捕捉在該資料列被修改之後 (AFTER DML) 才來執行 Trigger ，所以剛好滿足我們要記錄的資料表變動的行為。
Microsoft SQL Server 的 Trigger 內會包含有兩個特殊的虛擬資料表 inserted 與 deleted ; 這兩個資料表分別會擷取引發 Trigger 的資料列修改之前、後資料內容，當然它們會儲存單筆或多筆資料列更新的資料，下表說明了每個 DML 的操作內的 inserted、deleted 會包含的資料列之內容。
| 資料庫 DML | inserted | deleted |
| INSERT | 插入的新資料內容 | |
| UPDATE | 欲更新的新資料內容 | 更新前的舊資料內容 |
| DELETE | | 被刪除前的資料內容 |
當有新的資料列插入該資料表時，會觸發 INSERT 事件，會複製該新增資料列的內容到虛擬資料表 inserted ，但不會存在有虛擬資料表 deleted ;
若是某筆資料列被更新了，那麼會觸發 UPDATE 事件，並且會複製欲更新的新資料內容到虛擬資料表 inserted ，同時將舊資料列內容複製到虛擬資料表 deleted ;
刪除某筆資料列的話，將觸發 DELETE 事件，此時會將刪除前的資料列內容複製到虛擬資料表 deleted ，因為沒有資料新增或更新，所以，不會存在有虛擬資料表 inserted 。
在後面附上的面的 Trigger 內容中的 6~14 行，我便利用上述的原理來判斷觸發此 Trigger 的 DML 事件為何? 所以，我們需要對於資料庫的運作原理是有進一步了解，才有辦法靈活運用的。
上面的一堆資料庫的運作原理，主要是我想在同一個 Trigger 中同時使用多個 AFTER DML 事件，因為我不想維護同一類的 Trigger 內容，當然，也得看使用的情境，如果你的 AFTER INSERT 還包含其他的運算邏輯，那建議還是將內容分開兩份 Trigger 比較恰當。
再者，之前看過很多範例 (包含我以前自己寫的) ，若要紀錄所有的歷史紀錄大部分是新增跟原始資料表相同的欄位 (Schema) ，頂多在後面新增一個資料欄位來記錄該筆是，update、insert 或 delete，這樣雖然可行，但我想到萬一原始資料表的欄位有新增、修改或刪除，那麼這個歷史資料表也要同步更新，而我們又有可能忘了同步更新，而造成某些欄位的資料內容沒有被記錄到，我考量到這個問題，且想提高此 Trigger 的重複利用性，因此，我想將該資料列內的資料內容全部存成 XML 資料格式，將此 XML 資料放在一個 XML 資料型態的欄位即可，當然這有個缺點是，當要調閱某筆歷史資料時，你可能沒辦法很簡單的就查詢到該筆記錄的內容，可能需要再使用其他方法來解析出 XML 的內容才行，這部分在稍後我也會提供一個方法技巧來做查詢的。
要將資料列轉換成 XML 格式的資料其實不難，只要利用 SQL Server 2000 以後提供的 FOR XML ，便可輕易的將資料表資料轉換成需要的任何 XML 內容格式了 ，在下方的 Trigger 內容中的 17 與 20 行便有使用到此功能。
但為了對 XML 內容的查詢方便，我選擇了使用 FOR XML RAW, ELEMENTS, ROOT 來產出我要的格式。
下方是我實作的一個範例，首先要建立一個新的 LOG 資料表來存放紀錄 :
1: CREATE TABLE [dbo].[LOG](
2: [id] [uniqueidentifier] NOT NULL,
3: [dbname] [varchar](25) NULL,
4: [recoder] [xml] NULL,
5: [istype] [tinyint] NULL,
6: [updatedate] [datetime] NULL,
7: CONSTRAINT [PK_LOG] PRIMARY KEY CLUSTERED([id] ASC)
8: WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
9: ) ON [PRIMARY]
10: GO
11: ALTER TABLE [dbo].[LOG] ADD CONSTRAINT [DF_LOG_id] DEFAULT (newid()) FOR [id]
12: GO
13: ALTER TABLE [dbo].[LOG] ADD CONSTRAINT [DF_LOG_updatedate] DEFAULT (getdate()) FOR [updatedate]
14: GO
再來我們使用在 NorthWind 資料庫的 Customers 資料表內建立一個新的 Trigger ，此 Trigger 會在 Customers 資料表有被修改後觸發 :
1: CREATE TRIGGER [dbo].[trCustomers_UPDATE_INSERT_DELETE] ON [dbo].[Customers] AFTER UPDATE,INSERT,DELETE
2: AS
3: BEGIN
4: DECLARE @record XML
5: DECLARE @IsType TINYINT
6: SET @IsType=''
7: IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
8: SET @IsType = 1 --Insert
9:
10: IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
11: SET @IsType = 2 --Update
12:
13: IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
14: SET @IsType = 3 --Delete
15:
16: --只想記錄 UPDATE 事件更新資料前的舊資料
17: IF (@IsType = 1)
18: SET @record=(SELECT * FROM inserted FOR XML RAW('Customers'), ELEMENTS,ROOT)
19: ELSE
20: SET @record=(SELECT * FROM deleted FOR XML RAW('Customers'), ELEMENTS,ROOT)
21:
22: IF (@IsType <>'')
23: BEGIN
24: INSERT INTO [log]([dbname],[recoder],[istype])VALUES('Customers',@record ,@IsType)
25: END
26: END
上面的就已經做到我們要的儲存修改紀錄的功能了，不過美中不足的是這樣的資料結構不是那麼的方便查詢，必須要再利用 OPENXML 將 XML 資料取出來。
假設要追蹤某一筆記錄，你可以先使用 SQL 指令查詢到該筆記錄後，將 Log 資料表的 recoder 欄位的 XML 資料丟給 OPENXML 處理即可，如下方法 : (其實這方法可以應用在 Master and Detail view 情境中, 也就是你將之前的紀錄先以類似 GridView 的列表顯示所有紀錄後, 再使用 DetailsView 等將詳細的紀錄顯示出來)
1: DECLARE @xmldoc XML
2: SELECT @xmldoc = recoder FROM Log WHERE [id]='AF35C6FF-E36E-41D5-9158-911931ABDB19'
3:
4: DECLARE @hDoc AS INT
6: EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldoc
7: SELECT *
8: FROM OPENXML(@hDoc, '/root/Customers', 2)
9: WITH ([CustomerID] [nchar](5),
10: [CompanyName] [nvarchar](40),
11: [ContactName] [nvarchar](30),
12: [ContactTitle] [nvarchar](30),
13: [Address] [nvarchar](60),
14: [City] [nvarchar](15),
15: [Region] [nvarchar](15),
16: [PostalCode] [nvarchar](10),
17: [Country] [nvarchar](15),
18: [Phone] [nvarchar](24),
19: [Fax] [nvarchar](24))
20:
21: EXEC sp_xml_removedocument @hDoc
是不是看起來很簡單呀! 不過背後卻藏有不少的資料庫操作的技巧，我研究了幾天才找出比較適合我的解決方法，分享給各位參考囉!
