原文： http://www.dotblogs.com.tw/jameswu/archive/2009/07/23/9643.aspx
備份如下：
常被要求當資料表內的紀錄有被新增修改或刪除的時候，需要同步紀錄每次資料表變動的前後資料，這時候我們可以利用 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 資料表來存放紀錄 :
CREATE TABLE [dbo].[LOG](
[id] [uniqueidentifier] NOT NULL,
[dbname] [varchar](25) NULL,
[recoder] [xml] NULL,
[istype] [tinyint] NULL,
[updatedate] [datetime] NULL,
CONSTRAINT [PK_LOG] PRIMARY KEY CLUSTERED([id] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LOG] ADD CONSTRAINT [DF_LOG_id] DEFAULT (newid()) FOR [id]
GO
ALTER TABLE [dbo].[LOG] ADD CONSTRAINT [DF_LOG_updatedate] DEFAULT (getdate()) FOR [updatedate]
GO
再來我們使用在 NorthWind 資料庫的 Customers 資料表內建立一個新的 Trigger ，此 Trigger 會在 Customers 資料表有被修改後觸發 :
CREATE TRIGGER [dbo].[trCustomers_UPDATE_INSERT_DELETE] ON [dbo].[Customers] AFTER UPDATE,INSERT,DELETE
AS
BEGIN
DECLARE @record XML
DECLARE @IsType TINYINT
SET @IsType=''
IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
SET @IsType = 1 --Insert
IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
SET @IsType = 2 --Update
IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
SET @IsType = 3 --Delete
--只想記錄 UPDATE 事件更新資料前的舊資料
IF (@IsType = 1)
SET @record=(SELECT * FROM inserted FOR XML RAW('Customers'), ELEMENTS,ROOT)
ELSE
SET @record=(SELECT * FROM deleted FOR XML RAW('Customers'), ELEMENTS,ROOT)
IF (@IsType <>'')
BEGIN
INSERT INTO [log]([dbname],[recoder],[istype])VALUES('Customers',@record ,@IsType)
END
END
上面的就已經做到我們要的儲存修改紀錄的功能了，不過美中不足的是這樣的資料結構不是那麼的方便查詢，必須要再利用 OPENXML 將 XML 資料取出來。
假設要追蹤某一筆記錄，你可以先使用 SQL 指令查詢到該筆記錄後，將 Log 資料表的 recoder 欄位的 XML 資料丟給 OPENXML 處理即可，如下方法 : (其實這方法可以應用在 Master and Detail view 情境中, 也就是你將之前的紀錄先以類似 GridView 的列表顯示所有紀錄後, 再使用 DetailsView 等將詳細的紀錄顯示出來)
DECLARE @xmldoc XML
SELECT @xmldoc = recoder FROM Log WHERE [id]='AF35C6FF-E36E-41D5-9158-911931ABDB19'
DECLARE @hDoc AS INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldoc
SELECT *
FROM OPENXML(@hDoc, '/root/Customers', 2)
WITH ([CustomerID] [nchar](5),
[CompanyName] [nvarchar](40),
[ContactName] [nvarchar](30),
[ContactTitle] [nvarchar](30),
[Address] [nvarchar](60),
[City] [nvarchar](15),
[Region] [nvarchar](15),
[PostalCode] [nvarchar](10),
[Country] [nvarchar](15),
[Phone] [nvarchar](24),
[Fax] [nvarchar](24))
EXEC sp_xml_removedocument @hDoc
