在 SQL Server 觸發器中,inserted 和 deleted 是兩張虛擬表(內存驻留),由系統自動創建與維護,僅在觸發器執行期間可訪問,結構與觸發器綁定的基表完全一致。
| 操作 | inserted 表 | deleted 表 |
|---|---|---|
| INSERT | 包含新插入的行 | 空 |
| DELETE | 空 | 包含被刪除的行 |
| UPDATE | 包含更新後的新值 | 包含更新前的舊值 |
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
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
-- 更新訂單總額
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),若需啟用務必防止無限循環。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