萬盛學電腦網

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

SQL Server 2005 OUTPUT 子句實戰:INSERT/UPDATE/DELETE 同步回傳變更資料與表變數暫存

核心概念:inserted / deleted 虛擬表

  • inserted:INSERT/UPDATE 操作後的新值
  • deleted:UPDATE/DELETE 操作前的舊值
  • 結構與基礎表完全相同,儲存於記憶體,觸發器/OUTPUT 專用

四大操作範例

1. INSERT 同步回傳新增行 (含身分識別欄)

CREATE TABLE TestTB (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Province NVARCHAR(50),
    City NVARCHAR(50)
);

INSERT INTO TestTB (Province, City)
OUTPUT inserted.ID, inserted.Province, inserted.City
VALUES (N'廣東', N'深圳');
-- 結果:ID=1, Province=廣東, City=深圳

2. DELETE 回傳被刪除行

DELETE FROM TestTB
OUTPUT deleted.*
WHERE ID = 1;
-- 回傳完整被刪除行

3. UPDATE 同時回傳舊值與新值 (審計神器)

UPDATE TestTB 
SET Province = N'湖南', City = N'郴州'
OUTPUT 
    '更新前: ' + deleted.Province + deleted.City AS [Before],
    '更新後: ' + inserted.Province + inserted.City AS [After]
WHERE ID = 1;

4. OUTPUT INTO 表變數 — 後續處理靈活運用

DECLARE @DeletedLog TABLE (
    ID INT, Province NVARCHAR(50), City NVARCHAR(50), DeletedAt DATETIME DEFAULT GETDATE()
);

DELETE FROM TestTB
OUTPUT deleted.ID, deleted.Province, deleted.City, GETDATE()
INTO @DeletedLog
WHERE ID > 4;

SELECT * FROM @DeletedLog; -- 後續可 INSERT 正式審計表、發送通知等

進階用法

條件性輸出

UPDATE Products
SET Price = CASE WHEN Price > 100 THEN Price * 0.9 ELSE Price * 0.95 END
OUTPUT 
    inserted.ProductID,
    deleted.Price AS OldPrice,
    inserted.Price AS NewPrice,
    CASE WHEN deleted.Price > 100 THEN '大額折扣' ELSE '小額折扣' END AS DiscountType
INTO PriceChangeLog
WHERE CategoryID = 3;

MERGE 陳述式中使用 OUTPUT

MERGE TargetTable AS T
USING SourceTable AS S ON T.ID = S.ID
WHEN MATCHED THEN UPDATE SET T.Val = S.Val
WHEN NOT MATCHED THEN INSERT (ID, Val) VALUES (S.ID, S.Val)
OUTPUT $action, inserted.*, deleted.* INTO MergeAuditLog;
-- $action: 'INSERT' / 'UPDATE' / 'DELETE'

效能優勢

  • 單次掃描:避免「先 SELECT 再 DELETE/UPDATE」的雙重 IO
  • 原子性:變更與記錄同一交易,要么全成要么全滾回
  • 減少鎖持有時間:無需長交易保護中間結果

注意事項

  1. 目標表不可有觸發器 (INSTEAD OF 除外)
  2. 不可參考未包含在 OUTPUT 清單的計算欄位
  3. 大量資料輸出時考慮分批,避免記憶體壓力
  4. OUTPUT INTO 目標表不可參與 MERGE/UPDATE/DELETE 同一陳述式

結語

OUTPUT 子句是 SQL Server 2005 引入的「資料變更可見化」利器,配合 inserted/deleted 虛擬表與 INTO 表變數/實體表,優雅解決審計、歸檔、同步、回傳新增 ID 等高頻需求,取代觸發器與多語句邏輯,提升效能與可維護性。

copyright © 萬盛學電腦網 all rights reserved