SQL Server 交易與預存程序實戰：飯店房型/房間級聯刪除的正確實作

場景：飯店系統含 RoomType (主表) 與 Room (從表，FK 關聯)。刪除房型時需同步標記對應房間為已刪除 (軟刪除)，若任一步驟失敗全數回滾，保證資料一致性。

資料結構

-- RoomType (主表)
RoomTypeId (PK), TypeName, ..., TypeIsDel (BIT, 0=正常 1=刪除)

-- Room (從表)
RoomId (PK), RoomTypeId (FK), RoomName, ..., RoomIsDel (BIT)

預存程序：交易包裹雙表更新

CREATE PROCEDURE usp_DelRoomType
    @RoomTypeId INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Error INT = 0;

    BEGIN TRANSACTION;

    -- 1. 標記房型刪除
    UPDATE RoomType SET TypeIsDel = 1 WHERE RoomTypeId = @RoomTypeId;
    SET @Error += @@ERROR;
    IF @Error <> 0
    BEGIN
        ROLLBACK TRANSACTION;
        RETURN -1;
    END

    -- 2. 標記對應房間刪除
    UPDATE Room SET RoomIsDel = 1 WHERE RoomTypeId = @RoomTypeId;
    SET @Error += @@ERROR;
    IF @Error <> 0
    BEGIN
        ROLLBACK TRANSACTION;
        RETURN -1;
    END

    COMMIT TRANSACTION;
    RETURN 0;
END

關鍵設計要點

現代化寫法 (SQL Server 2005+)：TRY...CATCH

CREATE PROCEDURE usp_DelRoomType_v2
    @RoomTypeId INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        UPDATE RoomType SET TypeIsDel = 1 WHERE RoomTypeId = @RoomTypeId;
        UPDATE Room SET RoomIsDel = 1 WHERE RoomTypeId = @RoomTypeId;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        -- 記錄錯誤至 Log 表或拋出
        THROW; -- 或 RAISERROR
    END CATCH
END

資料層呼叫 (C# 範例)

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("usp_DelRoomType", conn) { CommandType = CommandType.StoredProcedure })
{
    cmd.Parameters.AddWithValue("@RoomTypeId", roomTypeId);
    conn.Open();
    int result = (int)cmd.ExecuteScalar(); // 0 成功, -1 失敗
}

替代方案比較

• FK CASCADE DELETE：硬刪除，不可逆，不支援軟刪除業務需求。
• 觸發器：隱性邏輯，除錯困難，效能開銷大。
• 應用層交易：跨網路往返，延遲高，連線中斷風險大。

預存程序 + 交易為「資料層封裝業務不變量」的最佳實踐。

1. 上一篇文章： SQL Prompt 5.3 智能提示與格式化工具：下載、安裝、離線破解完整圖文教學
2. 下一篇文章： SQL Server UPDATE 語法完整指南：從基礎到進階效能技巧

關於SqlServer

SQL Server 查詢目前連線數與工作階段資訊：sys.dm_exec_sessions 完整應用

掌握目前資料庫有多少連線、誰在連線、執行什麼查詢，是 DBA

SQL Server 2008 APPLY 運算子詳解：CROSS APPLY vs OUTER APPLY

APPLY 運算子概念 APPLY 允許將左側資料表的每一列

用 SQL 解決邏輯推理題：五人住樓層經典難題完整實作

題目描述 Baker、Cooper、Fletcher、Mil

PowerBuilder 6.0 實戰教學 (2-1-5)：向資料表新增記錄的兩種方法

背景 資料庫 TELEBOOK 與資料表 MYTELEBOO