星期五, 3月 11, 2011

[SQL] Trigger - inserted & deleted Table

deleted Table 和 inserted Table 為 Triiger 使用的兩個特殊暫存資料表(交易紀錄的檢視表),其欄位名稱、順序和資料型態都跟原來的 Table 一致,可以利用它們了解 INSERT、UPDATE 和 DELETE T-SQL 語法觸發 Triiger 時,插入、修改或是刪除哪些資料。

inserted Table 會儲存 INSERT 和 UPDATE 前的資料、deleted Table 則會儲存 UPDATE 後和 DELETE 的資料。
  •  利用表格表示
Trigger
  • 利用圖形表示
[SQL] Trigger - inserted & deleted Table - 2
SQL Server 會在 TempDB 中自動建立並管理,無法直接修改資料表內的資料或是修改其結構(EX:CREATE INDEX),當Trigger 結束時,暫存的 deleted Table 和 inserted Table 會自動消失。
  • 整合 inserted 和 deleted 並判斷類別(新增、更新或刪除)
利用 FULL JOIN 把 inserted Table 和 deleted Table 整合在一起,再透過判斷主要連結欄位是否為 NULL 或 相等,來判斷資料是新增、修改或是刪除。
SELECT 
  COALESCE(I.KeyFields,D.KeyFields) AS KeyFields, -- 利用 COALESCE() 來找出 KeyFields
  'Kind' = CASE
             WHEN D.KeyFields IS NULL THEN '新增' -- 利用 D.KeyFields IS NULL 來判斷是否為新增
             WHEN I.KeyFields IS NULL THEN '刪除' -- 利用 I.KeyFields IS NULL 來判斷是否為刪除
             WHEN I.KeyFields = D.KeyFields THEN '更新' -- 利用 I.KeyFields = D.KeyFields 來判斷是否為更新
           END
FROM 
  inserted AS I 
  FULL JOIN -- 利用 FULL JOIN 來連結兩個暫存資料表
  deleted AS D ON I.KeyFields AND D.KeyFields
  • 利用 EXISTS 來判斷資料是否存在 inserted 和 deleted
-- 新增  
IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)

-- 更新
IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)

-- 刪除
IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)

2 則留言:

Robert 提到...

Good!....不過,Table表中,是有新欄位"DEELTE"嗎?^^

TerryTsai 提到...

C.C.L 大,眼光實在是太銳利啦,已修正 ~~

張貼留言