Jason – 第 12 頁 – Jason's 程設BLOG

常被要求當資料表內的紀錄有被新增修改或刪除的時候，需要同步紀錄每次資料表變動的前後資料，這時候我們可以利用 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

2014 年 6 月 25 日2014 年 6 月 25 日 Jason 單眼皮女孩