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

来源：互联网发布：淘宝手机积分怎么查编辑：程序博客网时间：2024/06/05 18:34

--建立測試表格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表格更新、新增、刪除後觸發CREATETRIGGERdbo.TR_WIPPrd_Modify ondbo.WIPPrdAFTERUPDATE,INSERT,DELETEASBEGIN
    --表格異動資料時會產生暫存的inserted和deleted兩個表格    --兩個表格格式資訊皆與原表格相同    --inserted紀錄insert資料、update後資料    --deleted紀錄delete資料、update前資料
    --依據異動方式將異動資料新增到記錄檔    --inserted和deleted皆有資料表示為-UPDATE    IF EXISTS (select1frominserted)andEXISTS (select1fromdeleted)    BEGIN
        insertintoWIPPrd_Log select'DELETE',*fromdeleted        insertintoWIPPrd_Log select'INSERT',*frominserted    END    --inserted有資料deleted無資料表示為-INSERT    ELSEIF EXISTS (select1frominserted)andNotEXISTS (select1fromdeleted)        insertintoWIPPrd_Log select'INSERT',*frominserted    --inserted無資料deleted有資料表示為-DELETE    ELSEIFNOTEXISTS (select1frominserted)andEXISTS (select1fromdeleted)        insertintoWIPPrd_Log select'DELETE',*fromdeleted
END--測試--清空資料deletefromWIPPrddeletefromWIPPrd_log--新增資料insertintoWIPPrd values('11','TEST_11','11',GETDATE(),'Dean','10001')insertintoWIPPrd values('22','TEST_22','22',GETDATE(),'Dean','10001')--更新資料updateWIPPrdsetProdNo='33',PnameD='TEST_33'whereProdNo='22'--刪除資料deletefromWIPPrd--查詢記錄檔select*fromWIPPrd_log------------------------------------------------------------INSERT   11    TEST_11    11    2012-02-26 14:17:47.750    Dean    10001INSERT   22    TEST_22    22    2012-02-26 14:17:47.763    Dean    10001DELETE   22    TEST_22    22    2012-02-26 14:17:47.763    Dean    10001INSERT   33    TEST_33    22    2012-02-26 14:17:47.763    Dean    10001DELETE   33    TEST_33    22    2012-02-26 14:17:47.763    Dean    10001DELETE   11    TEST_11    11    2012-02-26 14:17:47.750    Dean    10001