萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 觸發器中的 deleted 與 inserted 表詳解

概述

在 SQL Server 觸發器中,inserted 和 deleted 是兩張虛擬表(內存驻留),由系統自動創建與維護,僅在觸發器執行期間可訪問,結構與觸發器綁定的基表完全一致。

各操作下的數據內容

操作inserted 表deleted 表
INSERT包含新插入的行空
DELETE空包含被刪除的行
UPDATE包含更新後的新值包含更新前的舊值

典型應用場景

1. 審計/日誌記錄

CREATE TRIGGER trg_Audit_Employee
ON Employee
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO Employee_Audit (EmpID, Action, ChangedData, ChangeTime)
    SELECT 
        COALESCE(i.EmpID, d.EmpID),
        CASE WHEN i.EmpID IS NULL THEN 'DELETE' 
             WHEN d.EmpID IS NULL THEN 'INSERT' 
             ELSE 'UPDATE' END,
        -- 這裡可構建變更詳情 XML/JSON
        GETDATE()
    FROM inserted i
    FULL JOIN deleted d ON i.EmpID = d.EmpID
END

2. 業務規則驗證(如防止負庫存)

CREATE TRIGGER trg_Check_Stock
ON OrderDetail
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT 1 FROM inserted i
        JOIN Product p ON p.ProductID = i.ProductID
        WHERE p.StockQty < i.Qty
    )
    BEGIN
        RAISERROR('庫存不足', 16, 1)
        ROLLBACK TRANSACTION
        RETURN
    END
END

3. 級聯操作/衍生數據維護

-- 更新訂單總額
CREATE TRIGGER trg_Update_OrderTotal
ON OrderDetail
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    UPDATE o SET TotalAmount = (
        SELECT SUM(UnitPrice * Qty) FROM OrderDetail WHERE OrderID = o.OrderID
    )
    FROM Orders o
    JOIN (
        SELECT OrderID FROM inserted
        UNION
        SELECT OrderID FROM deleted
    ) d ON o.OrderID = d.OrderID
END

關鍵注意事項

  • 集合操作思維:觸發器按語句級觸發,inserted/deleted 可能包含多行,切勿假設單行(如用標量變量賦值)。
  • 避免長事務:觸發器在原事務內執行,複雜邏輯會延長鎖持有時間,影響並發。
  • 遞歸觸發:默認關閉(RECURSIVE_TRIGGERS),若需啟用務必防止無限循環。
  • INSTEAD OF 觸發器:可用於視圖,執行替代原 DML 操作,此時基表不會自動變更,需在觸發器中顯式執行 DML。
  • 不可直接修改:inserted/deleted 僅讀,不能對其執行 INSERT/UPDATE/DELETE。

調試技巧

-- 在觸發器中臨時輸出調試信息
PRINT 'inserted rows: ' + CAST((SELECT COUNT(*) FROM inserted) AS VARCHAR)
PRINT 'deleted rows: ' + CAST((SELECT COUNT(*) FROM deleted) AS VARCHAR)
-- 或插入調試表
INSERT INTO DebugLog SELECT *, GETDATE() FROM inserted
copyright © 萬盛學電腦網 all rights reserved