MS SQL 利用Trigger紀錄Table異動資料

針對某些特定Table需要詳細記錄資料變更的歷史資訊，
可以利用Trigger及產生的inserted和deleted 臨時表格做紀錄！
作法可參考下列範例。

--建立測試表格
CREATE TABLE [dbo].[WIPPrd](
    [ProdNo] [varchar](16) NOT NULL,
    [PnameD] [nvarchar](100) NULL,
    [PType] [varchar](4) NULL,
    [InDay] [datetime] NULL,
    [UsrNo] [varchar](10) NULL,
    [UsrCo] [varchar](5) NULL) ON [PRIMARY]

--建立記錄檔
CREATE TABLE [dbo].[WIPPrd_Log](
    [MState] [varchar] (10) NOT NULL,
    [ProdNo] [varchar](16) NOT NULL,
    [PnameD] [nvarchar](100) NULL,
    [PType] [varchar](4) NULL,
    [InDay] [datetime] NULL,
    [UsrNo] [varchar](10) NULL,
    [UsrCo] [varchar](5) NULL )    
--建立TRIGGER-在WIPPrd表格更新、新增、刪除後觸發
CREATE TRIGGER dbo.TR_WIPPrd_Modify on dbo.WIPPrd AFTER UPDATE,INSERT,DELETE
AS
BEGIN   
    --表格異動資料時會產生暫存的inserted和deleted兩個表格
    --兩個表格格式資訊皆與原表格相同
    --inserted紀錄insert資料、update後資料
    --deleted紀錄delete資料、update前資料
   
    --依據異動方式將異動資料新增到記錄檔
    --inserted和deleted皆有資料表示為-UPDATE
    IF EXISTS (select 1 from inserted) and EXISTS (select 1 from deleted)
    BEGIN       
        insert into WIPPrd_Log select 'DELETE',* from deleted
        insert into WIPPrd_Log select 'INSERT',* from inserted
    END
    --inserted有資料deleted無資料表示為-INSERT
    ELSE IF EXISTS (select 1 from inserted) and Not EXISTS (select 1 from deleted)
        insert into WIPPrd_Log select 'INSERT',* from inserted
    --inserted無資料deleted有資料表示為-DELETE
    ELSE IF NOT EXISTS (select 1 from inserted) and EXISTS (select 1 from deleted)
        insert into WIPPrd_Log select 'DELETE',* from deleted

END
--測試
--清空資料 
delete from WIPPrd 
delete from WIPPrd_log
--新增資料
insert into WIPPrd values ('11','TEST_11','11',GETDATE(),'Dean','10001')
insert into WIPPrd values ('22','TEST_22','22',GETDATE(),'Dean','10001')
--更新資料
update WIPPrd set ProdNo='33',PnameD='TEST_33' where ProdNo='22'
--刪除資料
delete from WIPPrd
--查詢記錄檔
select * from WIPPrd_log
------------------------------------------------------------
INSERT    11    TEST_11    11    2012-02-26 14:17:47.750    Dean    10001
INSERT    22    TEST_22    22    2012-02-26 14:17:47.763    Dean    10001
DELETE    22    TEST_22    22    2012-02-26 14:17:47.763    Dean    10001
INSERT    33    TEST_33    22    2012-02-26 14:17:47.763    Dean    10001
DELETE    33    TEST_33    22    2012-02-26 14:17:47.763    Dean    10001
DELETE    11    TEST_11    11    2012-02-26 14:17:47.750    Dean    10001

參考自
~楓花雪岳~[SQL] Trigger - inserted & deleted Table
MSDN-使用 inserted 或 deleted 資料表
CSDN-Trigger中如何獲得更新前的數據

---------------------------------------------------------------------------
延伸閱讀-[MS SQL]-XML資料型態