Trigger

http://www.codeproject.com/Articles/25600/Triggers-SQL-Server

create trigger my_trigger
on my_table
after insert, update
as
begin
declare @dt datetime = getdate();
update a
set expire_date = CONVERT(DATE, @dt, 101),
    expire_time = CONVERT(TIME, @dt)
from my_table as a
join inserted as b 
on a.product_no = b.product_no; 
end
go  

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

http://www.dotblogs.com.tw/mis2000lab/archive/2014/06/12/sql-server-trigger.aspx