Oracle 視圖插入資料規則：非連接視圖直寫、連接視圖需 INSTEAD OF Trigger

視圖可更新性判斷

關鍵保留表 原則

連接視圖中，若某基礎表的主鍵在視圖中**保持唯一**，該表為「關鍵保留表」，可直接更新其非鍵欄位；非關鍵保留表的欄位**不可直接更新/插入**。

-- 範例：EMP (PK=EMPNO) 關聯 DEPT (PK=DEPTNO)
CREATE VIEW emp_dept AS
SELECT e.empno, e.ename, e.deptno, d.dname
FROM   emp e, dept d
WHERE  e.deptno = d.deptno;
-- EMP 為關鍵保留表（EMPNO 仍唯一），DEPT 非關鍵保留表
-- 可直接 UPDATE emp_dept SET ename='X' WHERE empno=1;
-- 不可直接 INSERT INTO emp_dept VALUES (..., 'New Dept'); -- 會報錯

解決方案：INSTEAD OF Trigger

CREATE OR REPLACE TRIGGER emp_dept_iot
INSTEAD OF INSERT ON emp_dept
FOR EACH ROW
BEGIN
  -- 1. 插入/更新 DEPT（非關鍵保留表）
  MERGE INTO dept d
  USING (SELECT :NEW.deptno AS deptno, :NEW.dname AS dname FROM dual) src
  ON (d.deptno = src.deptno)
  WHEN MATCHED THEN UPDATE SET dname = src.dname
  WHEN NOT MATCHED THEN INSERT (deptno, dname) VALUES (src.deptno, src.dname);

  -- 2. 插入 EMP（關鍵保留表）
  INSERT INTO emp (empno, ename, deptno)
  VALUES (:NEW.empno, :NEW.ename, :NEW.deptno);
END;
/
-- 現在可直接 INSERT INTO emp_dept VALUES (9999, 'NewEmp', 50, 'NewDept');

檢查視圖可更新欄位

SELECT * FROM user_updatable_columns WHERE table_name = 'EMP_DEPT';

結果顯示哪些欄位可直接更新，哪些需 Trigger。

最佳實務

1. 優先使用**非連接視圖**或**單表視圖**，天然可更新。
2. 連接視圖僅供**查詢**，寫入操作改用**存儲過程**或**INSTEAD OF Trigger**。
3. Trigger 中處理：主鍵衝突、外鍵驗證、業務邏輯、審計日誌。
4. 避免在視圖上建立複雜 Trigger 鏈，維護困難；改用 API 層封裝。