[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 的資料。

利用表格表示

利用圖形表示

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)

• MSDN 參考資料
• 使用 inserted 或 deleted 資料表
• COALESCE()
• 參考資料
• 使用 Trigger 紀錄資料表的新增、修改、刪除的行為 
• 如何在DML Trigger中判斷某些資料行是否被更新
• Stairway to Database Design Level 7: Triggers
• SQL SERVER – How to Use Instead of Trigger
• SQL Server Development Topic - Computed Columns vs Trigger
  
• Super SQL Server 討論 1、2
• If Your Trigger Uses UPDATE(), It’s Probably Broken.