Day 24 -「九頭蛇海德拉」有完沒完啊！來自 SQL Server 的鬼打牆

2026 iThome 鐵人賽

DAY 24

Software Development

諸神也搖頭的 Legacy Code： 30天 .NET 工程師生存之道系列第 24 篇

18th鐵人賽

newton

2026-08-24 00:00:14

104 瀏覽

分享至

在海克力士的十二項試煉中，第二項任務就是跑去勒拿的沼澤對付九頭蛇海德拉。

九顆頭本來就已經夠麻煩了，偏偏其中一個還殺不死，海克力士一開始拿起棍棒一顆一顆打掉，結果才剛打掉一顆，同一個地方馬上又長出兩顆。

海克力士就算再會打也沒有用，九頭蛇的頭只會越打越多，後來他找來伊奧勞斯幫忙，海克力士負責把頭打掉，伊奧勞斯就在旁邊用火把剛才打過的地方燒一遍，不讓它繼續長出新的頭。

等到那些會再長出來的頭全部處理完，海克力士才把最後殺不死的頭壓在大石頭底下，成功完成了這項試煉。

在 Legacy Code 中，有一種很常見的問題，就是表面上只是一個非常單純的 Bug，原本以為很簡單，以為找到問題後又發現，其實是另一個隱藏起來的地方造成的，再順著往下查，又發現其他地方也被牽連受影響，每次都以為終於找到了最終解答，卻又回到原點，簡直就像九頭蛇一樣處理不完...

金額不對

今日範例是一套業績佣金系統，業務人員完成的訂單會累積成當月業績，到了月底，系統會依照業績級距算出每位員工的佣金。畫面上的「佣金試算金額」代表目前根據業績與獎金算出的結果，月結排程會讀取這個數字，再把它寫成正式的付款資料。

而這次收到的 Issue 是這樣的：

根據陳雅芳（EmployeeId = 1）業務的資料，在2026-07 月結前的佣金試算金額是 42,000 元。第一次月結執行期間，負責排程的 worker 意外中止，平台因為沒有收到完成狀態而自動重跑。事後查詢發現，同一位員工出現 42,000 與 44,000 兩筆付款資料，而且目前畫面上的佣金試算金額已經變成 46,000 元。請確認為什麼排程重跑後，不只多了一筆付款，金額也跟著增加。

看到數字就覺得不太對，如果只是同一支月結重跑兩次，照理說應該得到兩筆一樣的 42,000，第二筆付款為什麼會變成 44,000，甚至現在畫面上的佣金試算金額又自己長成 46,000？這裡還沒有第三筆 46,000 的付款，46,000 只是 View 此刻算出的結果，看來這張 Issue 不只是「重複寫入」這麼簡單。

先抓到明顯的問題

我們先從排程的程式碼往下追，結果整段月結真的只有一行：

await conn.ExecuteAsync(
    "EXEC dbo.usp_CloseMonthlyCommission @SettlementMonth",
    new { SettlementMonth = "2026-07" });

在這個案例裡，付款寫入和排程的完成狀態不是同一件事。這行執行結束後，worker 還要把結果回報給排程平台，平台才會將這次工作標記為完成，從資料庫留下的 42,000 元付款與 worker log 可以對上，第一次其實已經完成，只是 worker 在回報前中止，所以付款留在資料庫裡，排程平台卻仍然把這次工作視為未完成，重新執行了同一個 job。

worker 重啟後只是把同一段流程再跑一次，程式碼本身沒有算佣金，也沒有另外補什麼資料，真正的工作全部交給 usp_CloseMonthlyCommission，所以我們繼續往 Stored Procedure 裡面看：

CREATE PROCEDURE dbo.usp_CloseMonthlyCommission
    @SettlementMonth char(7)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.CommissionPayouts
        (EmployeeId, SettlementMonth, Amount, ClosedAt)
    SELECT
        EmployeeId,
        SettlementMonth,
        PayableAmount,
        SYSUTCDATETIME()
    FROM dbo.v_CommissionSummary
    WHERE SettlementMonth = @SettlementMonth;
END;

Stored Procedure（預存程序，通常簡稱 SP）是一段預先寫好、存放在資料庫裡的 SQL 程式碼，它和程式語言的 method 很像，可以接收參數、包含複數個 SQL 陳述式，並且透過 EXEC SP名稱 @參數 一行指令就能執行完整的一段邏輯。

這下找到啦！這支 SP 每執行一次就 INSERT 一次，沒有檢查同一位員工、同一個月份是不是已經完成，Table 本身也沒有唯一約束，難怪排程平台一重跑就會多寫入一筆資料。

嗯？但這只能解釋為什麼有兩筆，還是解釋不了第二筆為什麼從 42,000 變成 44,000，SP 看起來並沒有自己加 2,000，它只會把 v_CommissionSummary.PayableAmount 當下查到的值原封不動寫進去，所以也許真正改變的其實是 View 的結果。

金額還是不對啊！

我們接著打開 v_CommissionSummary，這張 View 會加總已結案業績，依照級距算出基本佣金，最後再把特別獎金加進本期的佣金試算金額：

CREATE VIEW dbo.v_CommissionSummary
AS
WITH ClosedSales AS (
    SELECT
        EmployeeId,
        SettlementMonth,
        SUM(Amount) AS TotalSales
    FROM dbo.SalesOrders
    WHERE Status = N'Closed'
    GROUP BY EmployeeId, SettlementMonth
),
BonusTotals AS (
    SELECT
        EmployeeId,
        SettlementMonth,
        SUM(BonusAmount) AS SpecialBonus
    FROM dbo.Bonuses
    GROUP BY EmployeeId, SettlementMonth
)
SELECT
    e.EmployeeId,
    e.Name,
    s.SettlementMonth,
    s.TotalSales,
    CASE
        WHEN s.TotalSales >= 300000 THEN s.TotalSales * 0.12
        WHEN s.TotalSales >= 100000 THEN s.TotalSales * 0.08
        ELSE                             s.TotalSales * 0.05
    END AS BaseCommission,
    ISNULL(b.SpecialBonus, 0) AS SpecialBonus,
    CASE
        WHEN s.TotalSales >= 300000 THEN s.TotalSales * 0.12
        WHEN s.TotalSales >= 100000 THEN s.TotalSales * 0.08
        ELSE                             s.TotalSales * 0.05
    END + ISNULL(b.SpecialBonus, 0) AS PayableAmount
FROM dbo.Employees e
JOIN ClosedSales s
  ON s.EmployeeId = e.EmployeeId
LEFT JOIN BonusTotals b
  ON b.EmployeeId = s.EmployeeId
 AND b.SettlementMonth = s.SettlementMonth
WHERE e.IsActive = 1;

陳雅芳（EmployeeId = 1）在 2026-07 的已結案業績是 350,000，按照 12% 的級距，基本佣金確實是 42,000，公式沒有算錯，但現在 SpecialBonus 是 4,000，所以畫面上的佣金試算金額才會顯示 46,000。

但是那 4,000 又是哪裡來的？明明第一次寫入的時候沒有啊！

我們再往 Bonuses table 去查，剛好有兩筆各 2,000 元的資料，而且 SourcePayoutId 分別指向剛才的兩筆付款：

SELECT
    BonusAmount,
    SourcePayoutId
FROM dbo.Bonuses
WHERE EmployeeId = 1
  AND SettlementMonth = '2026-07'
ORDER BY SourcePayoutId;

BonusAmount  SourcePayoutId
-----------  --------------
2000         1
2000         2

看到這裡，我原本以為月結排程後面還有另一段 code 負責寫入 Bonuses，結果沿著程式碼、其他 SP 和 Bonuses 的寫入點一路找，卻完全找不到誰做了這兩次 INSERT。

資料不會自己從地上長出來，既然目前找到的 production code 都解釋不了，那看來只剩下資料庫了.....

原來是你啊！Trigger

程式碼裡怎麼找都找不到寫入點，加上又沒有相關文件，我們只好把目光轉回資料庫本身：

SELECT
    t.name AS TriggerName,
    OBJECT_SCHEMA_NAME(t.parent_id) AS TableSchema,
    OBJECT_NAME(t.parent_id) AS TableName
FROM sys.triggers t
WHERE t.parent_id = OBJECT_ID('dbo.CommissionPayouts');

果不其然找到一個名為 trg_CommissionPayout_GrantBonus 的 Trigger......沿著名稱我們這才看到這段 SQL：

CREATE TRIGGER dbo.trg_CommissionPayout_GrantBonus
ON dbo.CommissionPayouts
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Bonuses
        (EmployeeId, SettlementMonth, BonusAmount, SourcePayoutId)
    SELECT
        i.EmployeeId,
        i.SettlementMonth,
        2000,
        i.PayoutId
    FROM inserted i
    WHERE i.Amount >= 30000;
END;

Trigger（觸發器） 是一個掛在 Table 上的 SQL 程式碼，當這張 Table 發生特定事件（INSERT、UPDATE 或 DELETE），資料庫就會自動執行這段程式碼，完全不需要呼叫端主動呼叫。

不過「自動、無需呼叫端感知」同時也是 Trigger 最為人詬病的地方，它讓一次事務悄悄多做了別的事，而這件事從程式碼的角度看就完全看不到，只有進到資料庫才能發現它的存在。

哦，原來是你啊。

只要單筆付款超過 30,000，這支 AFTER INSERT Trigger 就會自動補一筆 2,000 元達標獎金，而且它用 inserted 做 set-based 處理，一次寫入多筆付款也能正常運作，單看這支 Trigger 甚至沒有明顯的語法問題，不過麻煩的是 Bonuses 剛好又會被 v_CommissionSummary 給讀取回去。

第一次月結時，View 算出 42,000，SP 寫入一筆 42,000 的付款，Trigger 隨即補上 2,000 元獎金，因此 View 的佣金試算金額變成 44,000，第一筆付款這時候其實已經成功寫入，只是 worker 在回報完成狀態前中止，排程平台才會重新執行同一個 job。

第二次執行時，SP 從 View 讀到的已經是 44,000，所以第二筆付款也跟著變成 44,000，Trigger 接著又補一筆 2,000 元獎金，View 最後就長成 Issue 上看到的 46,000。

查清楚了！

走到這裡，我們終於知道這個案例真正需要測哪些東西，四張 table、一張 View、一支 SP，還有剛剛才挖出來的 Trigger，我們依照昨天的方式來建立 test 專案，SQL Server container、Fixture 和 schema initializer 沿用昨天類似的做法，今天就不再重新贅述，相關程式碼都會放在今日範例的 Refactoring Branch中，歡迎各位安心取用 > <

比較特別的是我們必須在測試專案建置的時候，同時把 Table、View、SP 以及 Trigger 的 SQL 檔案帶進測試輸出目錄：

<ItemGroup>
    <Content Include="..\db\Tables\**\*.sql" Link="SqlSchema\Tables\%(Filename)%(Extension)" CopyToOutputDirectory="PreserveNewest" />
    <Content Include="..\db\Views\**\*.sql" Link="SqlSchema\Views\%(Filename)%(Extension)" CopyToOutputDirectory="PreserveNewest" />
    <Content Include="..\db\StoredProcedures\**\*.sql" Link="SqlSchema\StoredProcedures\%(Filename)%(Extension)" CopyToOutputDirectory="PreserveNewest" />
    <Content Include="..\db\Triggers\**\*.sql" Link="SqlSchema\Triggers\%(Filename)%(Extension)" CopyToOutputDirectory="PreserveNewest" />
</ItemGroup>

這段設定只負責複製檔案，真正套用 schema 時仍然要由 initializer 控制順序。

Characterization Test

測試先不要急著描述正確答案，我們先用 Characterization Test 把現在的 Bug 重現：

using src;

namespace test;

public sealed class CommissionClosingTests
    : IClassFixture<CommissionDbFixture>
{
    private const string SettlementMonth = "2026-07";

    private readonly CommissionRepository _repository;
    private readonly CommissionQueryService _queryService;
    private readonly CommissionSeeder _seeder;

    public CommissionClosingTests(CommissionDbFixture fixture)
    {
        _repository   = new CommissionRepository(fixture.ConnectionString);
        _queryService = new CommissionQueryService(fixture.ConnectionString);
        _seeder       = new CommissionSeeder(fixture.ConnectionString);
    }

    [Fact]
    public async Task 月結重試_目前會重複建立付款並改變試算金額()
    {
        await GivenEmployeeWithSalesOrderAsync();

        await _repository.CloseMonthlyCommissionAsync(SettlementMonth);
        await AssertClosingResultAsync(
	        count: 1, payoutAmount: 42_000m, payableAmount: 44_000m);

        await _repository.CloseMonthlyCommissionAsync(SettlementMonth);
        await AssertClosingResultAsync(
	        count: 2, payoutAmount: 44_000m, payableAmount: 46_000m);
    }

    private async Task GivenEmployeeWithSalesOrderAsync()
    {
        await _seeder.ResetAsync();
        await _seeder.InsertEmployeeAsync(employeeId: 1, name: "陳雅芳");
        await _seeder.InsertSalesOrderAsync(
	        employeeId: 1, settlementMonth: SettlementMonth, amount: 350_000);
    }

    private async Task AssertClosingResultAsync(
	    int count, decimal payoutAmount, decimal payableAmount)
    {
        var payouts = await _queryService.GetPayoutsAsync(SettlementMonth);
		
        var actualPayableAmount = await
		    _queryService.GetPayableAmountAsync(
			    employeeId: 1, settlementMonth: SettlementMonth);
				
        Assert.Equal(count, payouts.Count);
        Assert.Equal(payoutAmount, payouts[count - 1].Amount);
        Assert.Equal(payableAmount, actualPayableAmount);
    }
}

看到這支測試綠燈千萬不要太開心，第一次付款真的把佣金試算金額從 42,000 改成 44,000，第二次付款又讓下一次試算變成 46,000，我們先把現況弄清楚後，才寫得出第一條已經確認的安全需求，同一位員工、同一個月份，不管排程重試幾次，最多都只能有一筆付款。

確認問題之後，再根據已經明確的需求修正測試，使其紅燈：

[Fact]
public async Task 月結重試_不重複建立付款()
{
    await GivenEmployeeWithSalesOrderAsync();

    await _repository.CloseMonthlyCommissionAsync(SettlementMonth);
    await _repository.CloseMonthlyCommissionAsync(SettlementMonth);
        
    await AssertClosingResultAsync(
	    count: 1, payoutAmount: 42_000m, payableAmount: 44_000m);
}

確認紅燈後我們接下來修正它！

聊聊 SP

現在這個系統的月結資料操作本來就封裝在 usp_CloseMonthlyCommission，如果只是為了防重複，又把付款資料結構與查詢條件拉回到程式碼去做，反而會讓這次修改跨越更多邊界。因此在還沒釐清整套月結規則以前，先把最小修正留在既有的 transaction boundary 裡，會是相對保守的選擇。

這裡可以聊到 SP 在企業系統裡一種常見的權衡，SP 封裝 DB 細節後，呼叫端只需要知道 EXEC usp_CloseMonthlyCommission @SettlementMonth，應用程式碼跟資料庫之間就多了一層隔離，特別是在 DB 跨多個服務或有嚴格資料庫權限管控的環境，只開放執行 SP 的權限，而不是讓應用程式直接操作 Table。

相反的，當然 SP 也有它的代價，當邏輯分散在兩個地方、版控和 code review 需要刻意維護，所以團隊要特別注意使用 SP，不要拿來當作太多業務邏輯的載體。

聊聊 Trigger

Trigger 本身不是壞東西，它常被用在「每次這個 Table 有異動，就要連帶做某件事」的場景，例如每次 INSERT 就自動寫一筆異動日誌到稽核 Table，或者某個欄位更新後要同步更新另一張衍生 Table，這類邏輯放在 Trigger 的好處是，不管是哪支程式碼、哪個 SP 觸發的，都一定跑得到，不會漏掉。

問題在於「不管誰觸發都跑得到」這件事是雙面刃，Trigger 對呼叫端來說可見度幾乎為零，一次操作背後可能默默寫了好幾張 Table，從應用程式的角度看就只是「我寫了一筆付款」，但實際上資料庫已經悄悄做了一堆其他事情。

因此，Trigger 真的要小心使用，一旦被過度濫用，什麼意想不到的鬼故事都有可能發生 XD

所以在 Legacy Code 裡看到滿滿的 Trigger，我們要先帶著尊敬的心，先去理解它，時機成熟後，再處理掉它！

看似不太專業的修復

既然循環已經找到了，那是不是直接把 Trigger 刪掉就好了？先忍住，真的不要看到 Trigger 就手癢！這支 Trigger 很有可能還支援其他模組，也可能就是一條從來沒有寫進需求文件的正式規則，什麼都沒問就直接拔掉，很可能只是把今天的月結事故換成下個月的獎金事故。

我們現在不知道達標獎金算本期還是下期？Trigger 的存在是正式規則還是歷史補丁？第一筆的金額到底對不對？這些都還不知道，在這種情況下，最危險的事就是想一口氣把所有問題都解決，因為我們根本不知道哪些是問題。

但有一件事是確定的，同一位員工、同一個月份，絕對不能有兩筆付款，這不需要問任何人，Issue 已經說得很清楚，而這個問題的源頭就在 SP 沒有擋重複寫入。

所以我們就先只修正這一件事情。

既然我們決定要直接修改 SP，讓它在寫入前先確認這個月份是否已結算過，另外也能替 CommissionPayouts 加上 unique index 當作最後一道防線：

CREATE UNIQUE INDEX UX_CommissionPayouts_Employee_Month
ON dbo.CommissionPayouts (EmployeeId, SettlementMonth);

CREATE OR ALTER PROCEDURE dbo.usp_CloseMonthlyCommission
    @SettlementMonth char(7)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.CommissionPayouts
        (EmployeeId, SettlementMonth, Amount, ClosedAt)
    SELECT
        s.EmployeeId,
        s.SettlementMonth,
        s.PayableAmount,
        SYSUTCDATETIME()
    FROM dbo.v_CommissionSummary s
    WHERE s.SettlementMonth = @SettlementMonth
      AND NOT EXISTS (
          SELECT 1
          FROM dbo.CommissionPayouts p
          WHERE p.EmployeeId = s.EmployeeId
            AND p.SettlementMonth = s.SettlementMonth
      );
END;

完成後再跑跑看測試確保綠燈！

NOT EXISTS 處理正常的重試路徑，unique index 才是 database level 最後保險，不過若出現競爭問題的話，可能會有其中一個 INSERT 因 unique index 衝突而失敗，因此 production 還必須決定這種衝突應該被當成「已完成」還是真正的 exception。

「看似不專業」的修法，反而是刻意的，只修我們確定的問題，不確定的先放著，比起一口氣把所有問題都解決，卻順手改掉我們不了解的東西，這樣其實更安全。

不過這段 SQL 在 container 裡通過，不代表可以直接拿去正式環境執行，只要 production 早就存在同一位員工、同一月份的重複付款，unique index 就會建立失敗，哪些資料是真的重複、要保留哪一筆、需不需要財務確認，都得先討論後才能有執行計畫。

這個止血方案可行，但要真正拆除掉這個麻煩又不好找的 Chain，還得先從業務出發把幾個問題問清楚，跟團隊討論過後，我們才有資格決定要把 Bonus 計算搬進 SP 還是要改成明確的 workflow，還是調整 View 的資料來源...等等。

我自己常常看到 Trigger 的時候也很容易想說「要不就全部搬回程式碼做不就好了？」，但 Legacy Code 麻煩就是在這裡，當規則藏到很深，通常都有它的原因的，若擅自移除可能會同時爆掉好幾個功能，先用測試把效果看見，再決定要在哪裡把循環切斷，至少我們可以在重構的時候有一份保障。

總結

今天從一張 Issue 追到 SP，再從 SP 追到 View，從 View 追到 Bonuses，最後從 Bonuses 挖出一支沒有任何人提過的 Trigger，中間繞了好大一圈，感覺每找到一條線索，就又冒出幾個問號。

圖片擷取自網路

所以難怪學長都常說：「先看有沒有 Trigger！」

這個案例還只有一支，現實中的系統可能是 A Table 的 INSERT 更新 B Table，B Table 又觸發另一支 Trigger 去寫 C Table，最後 C Table 再回頭異動 A Table，當業務規則大量藏在這種 Trigger chain 裡，呼叫端只看得到「我寫了 A」，很難馬上發現就是從這裡開始互相干擾的。

所以，別用太多啊！