SQL Trigger 錯誤「聚合不應出現在 UPDATE 集合列表中」：用變數暫存 SUM 結果

錯誤 SQL

CREATE TRIGGER upd_grade ON Reports FOR UPDATE, INSERT AS
UPDATE Students
SET CreditHours = (SELECT SUM(CreditHours) FROM Courses WHERE Cno IN (...)) -- 直接用子查詢聚合
WHERE Students.Sno IN (SELECT Sno FROM inserted);

錯誤：**聚合函數（SUM）不能直接出現在 UPDATE 的 SET 子句右側**（部分資料庫如 SQL Server 限制）。

修正：宣告變數接收聚合值

CREATE TRIGGER upd_grade ON Reports FOR UPDATE, INSERT AS
BEGIN
    DECLARE @TotalHours INT;

    -- 1. 先計算聚合值存入變數
    SELECT @TotalHours = SUM(CreditHours)
    FROM Courses
    WHERE Cno IN (
        SELECT Cno FROM Reports
        WHERE Grade >= 60 AND Sno IN (SELECT Sno FROM inserted)
    );

    -- 2. 再用變數更新
    UPDATE Students
    SET CreditHours = @TotalHours
    WHERE Sno IN (SELECT Sno FROM inserted);
END

進階：支援多筆 inserted（集合式思維）

上例假設 `inserted` 只有一筆。正確觸發器應處理多筆：

CREATE TRIGGER upd_grade ON Reports FOR UPDATE, INSERT AS
BEGIN
    SET NOCOUNT ON;

    UPDATE s
    SET CreditHours = ca.TotalHours
    FROM Students s
    JOIN (
        SELECT r.Sno, SUM(c.CreditHours) AS TotalHours
        FROM inserted i
        JOIN Reports r ON r.Sno = i.Sno
        JOIN Courses c ON c.Cno = r.Cno
        WHERE r.Grade >= 60
        GROUP BY r.Sno
    ) ca ON ca.Sno = s.Sno;
END

關鍵點

• **聚合結果先存變數 / 用 JOIN 衍生表**，不可直接放在 `SET col = (SELECT SUM...)`。
• 觸發器必須**集合式處理 `inserted`/`deleted`**，不要假設單筆。
• 使用 `SET NOCOUNT ON` 減少網路往返。

總結

「聚合不應出現在 UPDATE 集合列表中」→ 先用變數或衍生表計算聚合值，再執行 UPDATE。集合式寫法效能更好、邏輯更正確。