防禦式 SQL 更新

系統維運難免會有直接修改正式台資料庫的需求，這類更新程序需要被嚴謹規範，萬一指令 WHERE 條件下錯，更新對象從一筆變成一萬筆，可不是亂著玩的。

為防範出錯，除了制定申請程序、要求指令複核，通常還要保留更新前後資料狀態，一方面可供人為確認，另一方面作為佐證。不過，要防止更新結果與預期不一致，除了靠人為把關(延伸閱讀：小心駛得萬年船 - SQL指令保險栓)，在撰寫 SQL 更新指令時採取防禦性設計概念，會是更好的做法。

用個實例說明，假設我有個 Product 資料庫：

資訊部門接獲業務單位指示，要將原本編號為 1 的產品由 980 元調成 990，更新指令怎麼寫？

UPDATE Product SET Price = 990 WHERE Id = 1？

可行。但請考慮以下情況：

1. 資料庫裡沒 Id = 1 這筆 (申請單位搞錯資料庫?)
2. Id = 1 其實是 Notebook，原價 12,500 (業務單位給錯產品編號)
3. 它的價格前陣子早已被調成 1000 元 (業務單位接收到錯誤訊息給錯指示)

依我的看法，以上三種狀況 UPDATE 指令都不該執行，應退回申請單位釐清問題根源，說不定還能及時阻止另一場災難。

基於以上考量，一個有效防呆設計是在更新指令前後加入檢查，當遇到檢查結果不如預期就取消更新作業並拋出錯誤。

以 SQL Server 而言，我們可以用 BEGIN TRAN 啟用交易將相關更新包在一起，配合 SET XACT_ABORT ON 指定遇到錯誤即 Rollback 取消整批更新動作。在執行過程，我們可加入自訂檢查邏輯，一旦察覺結果與預期不同，使呼叫 THROW 丟出錯誤(注意：RAISERROR 不會觸發 XACT_ABORT，要用 THROW)觸發取消交易。

以本案例可寫成：

SET XACT_ABORT ON
BEGIN TRAN

--實驗用，插入一筆判別 Commit 或 Rollback
INSERT INTO Product VALUES (255,'Committed', -1);

SELECT * FROM Product WHERE Id = 1;
IF @@ROWCOUNT <> 1 THROW 51000, N'Id=1 not found', 1;
UPDATE Product SET Price = 990 WHERE Id = 1 AND Price = 980;
IF @@ROWCOUNT <> 1 THROW 51000, N'Id=1, Price=980 not matched', 1;
SELECT * FROM Product WHERE Id = 1;

COMMIT TRAN

正常執行的話會像這樣：

修改前後各 SELECT 一次，對照佐證也有了。

正常流程測完，先 DELETE Product WHERE Price < 0 將觀察用的 Id = 255 移除，再來動點手腳做實驗。

實驗 1，將 Id = 1 改成 Notebook，價格 12500：

如上圖所示，發生 Id=1, Price=980 not matched 錯誤，資料不會更新。(另開視窗 SELECT * FROM Product 並沒有 Id = 255，證實交易被 Rollback)

實驗 2，將 Id = 1 刪除：

得到 Id=1 not found，一樣不會執行更新。

透過這種方式，我們就可讓資料更新程序更嚴謹，降低失誤風險。以上提供大家參考。

Tutorial of how to update SQL in defensive way to decrease risk of mistakes.