T-SQL OUTPUT 子句實戰：捕獲 INSERT/UPDATE/DELETE 影響行詳細資訊

前言

SQL Server 2005 引入 OUTPUT 子句，讓 DML 陳述式在修改資料的同時，將「修改前/後」的資料行直接回傳給用戶端或存入資料表/表變數。這對稽核追蹤、同步邏輯、應用層即時回饋極具價值。本文以實戰範例詳細解析三大 DML 的 OUTPUT 用法。

一、基礎語法結構

OUTPUT  [ INTO { @table_variable | output_table } ]
[ WHERE ... ];

• INSERTED：新資料列（INSERT/UPDATE 後）
• DELETED：舊資料列（UPDATE/DELETE 前）
• INTO 目標必須為**資料表或表變數**，不可為純標量變數。

二、INSERT ... OUTPUT 實戰

2.1 直接回傳給用戶端

INSERT INTO book (bkno, bkname, author, publisher, pubdate, isbn, price, remark)
OUTPUT INSERTED.*
VALUES (106, N'數字圖像處理', N'宋青書', N'科學出版社', '2006/7/1', '200607100', 25.00, N'較差');

回傳包含身分識別欄位、計算欄位、預設值等完整新列。

2.2 導入表變數供後續邏輯使用

DECLARE @NewBooks TABLE (bkno INT, bkname NVARCHAR(50), author NVARCHAR(8));

INSERT INTO book (bkno, bkname, author, publisher, pubdate, isbn, price, remark)
OUTPUT INSERTED.bkno, INSERTED.bkname, INSERTED.author INTO @NewBooks
SELECT 104, N'JSP網站開發教程', N'趙敏', N'電子工業出版社', '2002/10/11', '200210201', 35.00, N'較好'
UNION ALL
SELECT 105, N'C++Builder程序設計', N'俞蓮舟', N'人民郵電出版社', '2007/1/1', '200701111', 53.00, N'一般';

SELECT * FROM @NewBooks;  -- 後續可用於寄信、記錄稽核等

三、DELETE ... OUTPUT 實戰

DELETE FROM book
OUTPUT DELETED.*
WHERE bkno = 104;

• 回傳被刪除的完整列，適用於軟刪除前備份、觸發器替代方案。

四、UPDATE ... OUTPUT 實戰：捕獲修改前後值

DECLARE @PriceChange TABLE (
    preprice NUMERIC(10,2),
    posprice NUMERIC(10,2),
    bkno INT,
    changedate DATETIME DEFAULT GETDATE()
);

UPDATE book
SET price = price * 1.3
OUTPUT DELETED.price AS preprice,
       INSERTED.price AS posprice,
       INSERTED.bkno
INTO @PriceChange;

SELECT * FROM @PriceChange;

典型應用：價格異動記錄、庫存變動追蹤、欄位級稽核。

五、進階技巧與注意事項

六、多資料列更新範例

UPDATE testbook
SET price = price * 0.7,
    remark = N'折扣促銷'
WHERE pubdate <= '2000-01-01';

七、大值類型 (MAX) 更新技巧

UPDATE file_table
SET FileName = 'Img100.jpg',
    DOCUMENT = (SELECT * FROM OPENROWSET(BULK 'C:\Img100.jpg', SINGLE_BLOB) AS Doc)
WHERE FileType = '.jpg';

八、DELETE vs TRUNCATE 重溫

需要稽核軌跡時**必須用 DELETE + OUTPUT**；單純清空大表用 TRUNCATE。

九、查詢資料表結構輔助腳本

SELECT 
    c.name AS 欄位名,
    t.name AS 型別,
    c.max_length AS 長度
FROM sys.all_columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE c.object_id = OBJECT_ID('dbo.student');