SQL Server 視圖與函數無法直接 UPDATE/INSERT 解決方案

錯誤現象

對檢視表或函數 'XXX' 的更新或插入失敗，因其包含衍生欄位或常數欄位。

根本原因

建立的 View 包含計算欄位 (如 AVG(grade))、常數、運算式，這些欄位無法直接寫入，導致 View 不可更新。

-- 範例：不可更新的 View
CREATE VIEW D_S_G (sno, avg_grade) 
AS 
SELECT sno, AVG(grade) FROM SC GROUP BY sno;
-- avg_grade 為聚合計算欄位，無法 INSERT/UPDATE

解決方案：建立實體表取代 View

-- 1. 建立同結構實體表 (無資料)
SELECT * INTO d_s_t FROM d_s_g WHERE 1 <> 1;

-- 2. 將 View 資料複製進實體表
INSERT INTO d_s_t SELECT * FROM d_s_g;

-- 3. 後續對 d_s_t 進行 INSERT/UPDATE/DELETE

可更新 View 的條件 (參考)

View 可直接修改需同時滿足：

• 單一基礎表 (無 JOIN、UNION、子查詢)
• 無 GROUP BY、HAVING、DISTINCT、聚合函數
• 無計算欄位、常數欄位
• 所有修改欄位直接對應基礎表欄位
• WITH CHECK OPTION 可強制檢查

INSTEAD OF 觸發器進階方案

若必須保留 View 介面，可建立 INSTEAD OF INSERT/UPDATE/DELETE 觸發器攔截操作並轉寫入基礎表：

CREATE TRIGGER tr_v_D_S_G_Insert ON D_S_G
INSTEAD OF INSERT
AS
BEGIN
    -- 解析 inserted 資料，寫入基礎表 SC
    INSERT INTO SC (sno, grade)
    SELECT sno, avg_grade FROM inserted;  -- 需業務邏輯反推
END

最佳實務建議

1. View 定位為查詢介面，不作為資料修改入口
2. 資料修改直接操作基礎表或透過預存程序
3. 若需「可寫入的聚合視圖」，考慮索引檢視表 (Indexed View) + INSTEAD OF 觸發器
4. ETL/資料遷移階段：SELECT INTO 建立暫存實體表最簡單可靠