--建立測試表格
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 当表格insert,delete 时触发记录历史数据
最新推荐文章于 2020-08-05 13:56:08 发布