SQL trigger 当表格insert,delete 时触发记录历史数据

--建立測試表格
CREATETABLE [dbo].[WIPPrd](
    [ProdNo] [varchar](16)NOTNULL,
    [PnameD] [nvarchar](100) NULL,
    [PType] [varchar](4)NULL,
    [InDay] [datetime] NULL,
    [UsrNo] [varchar](10)NULL,
    [UsrCo] [varchar](5)NULL)ON[PRIMARY]
 
--建立記錄檔
CREATETABLE [dbo].[WIPPrd_Log](
    [MState] [varchar] (10) NOTNULL,
    [ProdNo] [varchar](16)NOTNULL,
    [PnameD] [nvarchar](100) NULL,
    [PType] [varchar](4)NULL,
    [InDay] [datetime] NULL,
    [UsrNo] [varchar](10)NULL,
    [UsrCo] [varchar](5)NULL)   
--建立TRIGGER-在WIPPrd表格更新、新增、刪除後觸發
CREATETRIGGER dbo.TR_WIPPrd_Modify ondbo.WIPPrd AFTERUPDATE,INSERT,DELETE
AS
BEGIN  
    --表格異動資料時會產生暫存的inserted和deleted兩個表格
    --兩個表格格式資訊皆與原表格相同
    --inserted紀錄insert資料、update後資料
    --deleted紀錄delete資料、update前資料
    
    --依據異動方式將異動資料新增到記錄檔
    --inserted和deleted皆有資料表示為-UPDATE
    IF EXISTS (select1 frominserted) andEXISTS (select1 fromdeleted)
    BEGIN      
        insertinto WIPPrd_Log select'DELETE',*fromdeleted
        insertinto WIPPrd_Log select'INSERT',*frominserted
    END
    --inserted有資料deleted無資料表示為-INSERT
    ELSEIF EXISTS (select1 frominserted) andNot EXISTS (select1 fromdeleted)
        insertinto WIPPrd_Log select'INSERT',*frominserted
    --inserted無資料deleted有資料表示為-DELETE
    ELSEIF NOTEXISTS (select1 frominserted) andEXISTS (select1 fromdeleted)
        insertinto WIPPrd_Log select'DELETE',*fromdeleted
 
END
--測試
--清空資料
deletefrom WIPPrd
deletefrom WIPPrd_log
--新增資料
insertinto WIPPrd values('11','TEST_11','11',GETDATE(),'Dean','10001')
insertinto WIPPrd values('22','TEST_22','22',GETDATE(),'Dean','10001')
--更新資料
updateWIPPrd setProdNo='33',PnameD='TEST_33'where ProdNo='22'
--刪除資料
deletefrom WIPPrd
--查詢記錄檔
select* fromWIPPrd_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