Oracle INSTEAD OF 觸發器深度解析：讓複雜視圖支援 INSERT/UPDATE/DELETE 的完整實作模式

Oracle INSTEAD OF 觸發器深度解析：讓複雜視圖支援 INSERT/UPDATE/DELETE 的完整實作模式

當視圖包含多表連接、彙總函數、DISTINCT、集合運算或虛擬欄位時，Oracle 判定為「複雜視圖」並原生禁止 DML。INSTEAD OF 觸發器正是為此設計：它攔截 對視圖的 DML 操作，改由開發者撰寫的 PL/SQL 邏輯決定如何同步更新底層基礎表。本文提供從單一操作到三合一觸發器的完整演進範例，並總結不可更新視圖的判定標準。

為什麼需要 INSTEAD OF 觸發器？

簡單視圖 (單表、無彙總/連接) Oracle 能自動將 DML 映射回基礎表。但複雜視圖存在「一對多」、「多對一」或「計算欄位無對應基礎欄位」等語意模糊，Oracle 無法安全自動轉譯，故要求開發者明確定義行為。

觸發器建立語法

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF INSERT OR UPDATE [OF col1, col2...] OR DELETE
ON view_name
[FOR EACH ROW]
[WHEN (condition)]
DECLARE
    -- 宣告區
BEGIN
    -- 程式碼區
    -- 使用 :NEW 欄位取得新值 (INSERT/UPDATE)
    -- 使用 :OLD 欄位取得舊值 (UPDATE/DELETE)
    -- 使用 INSERTING / UPDATING / DELETING 布林函數判斷操作類型
END [trigger_name];

關鍵差異：
① 無 BEFORE/AFTER，直接用 INSTEAD OF；
② 作用對象是 VIEW 而非 TABLE；
③ 僅支援 FOR EACH ROW (列級)，不支援陳述式級。

範例環境：員工-部門連接視圖

CREATE OR REPLACE VIEW v_emp_dept AS
SELECT e.empno, e.ename, e.job, e.sal, e.deptno, d.dname, d.loc
FROM   emp e JOIN dept d ON e.deptno = d.deptno;

階段 1：單獨 INSERT 觸發器

CREATE OR REPLACE TRIGGER v_emp_dept_ins_trg
INSTEAD OF INSERT ON v_emp_dept
FOR EACH ROW
DECLARE
    v_emp_cnt  NUMBER;
    v_dept_cnt NUMBER;
BEGIN
    -- 檢查員工是否已存在
    SELECT COUNT(*) INTO v_emp_cnt FROM emp WHERE empno = :NEW.empno;
    -- 檢查部門是否已存在
    SELECT COUNT(*) INTO v_dept_cnt FROM dept WHERE deptno = :NEW.deptno;

    IF v_emp_cnt = 0 THEN
        INSERT INTO emp (empno, ename, job, sal, deptno)
        VALUES (:NEW.empno, :NEW.ename, :NEW.job, :NEW.sal, :NEW.deptno);
    END IF;

    IF v_dept_cnt = 0 THEN
        INSERT INTO dept (deptno, dname, loc)
        VALUES (:NEW.deptno, :NEW.dname, :NEW.loc);
    END IF;
END;
/

-- 測試
INSERT INTO v_emp_dept (empno, ename, job, sal, deptno, dname, loc)
VALUES (7777, '張三豐', 'CLERK', 800, 77, '活動部', '深圳');

階段 2：單獨 UPDATE 觸發器

CREATE OR REPLACE TRIGGER v_emp_dept_upd_trg
INSTEAD OF UPDATE ON v_emp_dept
FOR EACH ROW
BEGIN
    UPDATE emp SET ename = :NEW.ename, job = :NEW.job, sal = :NEW.sal
    WHERE empno = :NEW.empno;

    UPDATE dept SET dname = :NEW.dname, loc = :NEW.loc
    WHERE deptno = :NEW.deptno;
END;
/

-- 測試
UPDATE v_emp_dept SET ename = '任我行', sal = 2000, dname = '魔教' WHERE empno = 7777;
COMMIT;

階段 3：單獨 DELETE 觸發器

CREATE OR REPLACE TRIGGER v_emp_dept_del_trg
INSTEAD OF DELETE ON v_emp_dept
FOR EACH ROW
DECLARE
    v_emp_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_emp_cnt FROM emp WHERE empno = :OLD.empno;
    IF v_emp_cnt > 0 THEN
        DELETE FROM emp WHERE empno = :OLD.empno;
        -- 視業務決定是否連帶刪除部門 (通常不刪)
    END IF;
END;
/

-- 測試
DELETE FROM v_emp_dept WHERE empno = 7777;
COMMIT;

階段 4：三合一綜合觸發器 (生產環境推薦)

CREATE OR REPLACE TRIGGER v_emp_dept_iod_trg
INSTEAD OF INSERT OR UPDATE OR DELETE ON v_emp_dept
FOR EACH ROW
DECLARE
    v_emp_cnt  NUMBER;
    v_dept_cnt NUMBER;
BEGIN
    IF INSERTING THEN
        SELECT COUNT(*) INTO v_emp_cnt FROM emp WHERE empno = :NEW.empno;
        SELECT COUNT(*) INTO v_dept_cnt FROM dept WHERE deptno = :NEW.deptno;

        IF v_emp_cnt = 0 THEN
            INSERT INTO emp (empno, ename, job, sal, deptno)
            VALUES (:NEW.empno, :NEW.ename, :NEW.job, :NEW.sal, :NEW.deptno);
        END IF;
        IF v_dept_cnt = 0 THEN
            INSERT INTO dept (deptno, dname, loc)
            VALUES (:NEW.deptno, :NEW.dname, :NEW.loc);
        END IF;

    ELSIF UPDATING THEN
        UPDATE emp SET ename = :NEW.ename, job = :NEW.job, sal = :NEW.sal
        WHERE empno = :NEW.empno;
        UPDATE dept SET dname = :NEW.dname, loc = :NEW.loc
        WHERE deptno = :NEW.deptno;

    ELSIF DELETING THEN
        SELECT COUNT(*) INTO v_emp_cnt FROM emp WHERE empno = :OLD.empno;
        IF v_emp_cnt > 0 THEN
            DELETE FROM emp WHERE empno = :OLD.empno;
        END IF;
    END IF;
END;
/

-- 完整測試流程
INSERT INTO v_emp_dept VALUES (7777, '張三豐', 'CLERK', 800, 77, '活動部', '深圳');
SELECT * FROM v_emp_dept WHERE empno = 7777;

UPDATE v_emp_dept SET ename = '任我行', sal = 2000, dname = '魔教' WHERE empno = 7777;
COMMIT;
SELECT * FROM v_emp_dept WHERE empno = 7777;

DELETE FROM v_emp_dept WHERE empno = 7777;
COMMIT;
SELECT * FROM v_emp_dept WHERE empno = 7777;
SELECT * FROM emp WHERE empno = 7777;

不可更新視圖判定標準 (Oracle 文件規範)

視圖包含下列任一結構，即為不可更新視圖，必須 建立 INSTEAD OF 觸發器才能執行 DML：

1. 集合運算子：UNION, UNION ALL, INTERSECT, MINUS
2. 群組/彙總函數：MIN, MAX, SUM, AVG, COUNT 等
3. GROUP BY, CONNECT BY, START WITH, HAVING
4. DISTINCT 關鍵字
5. 多表連接 (JOIN) — 含外連接
6. CASE / DECODE / 虛擬欄位運算式
7. 子查詢在 SELECT 清單或 WHERE 中 (視複雜度)

進階技巧與注意事項

• 觸發器內避免對同一視圖再發動 DML，否則會遞迴觸發 (ORA-04091/ORA-04092)。若需同步其他視圖，改操作基礎表。
• 使用 :NEW/:OLD 時注意資料型別，視圖欄位若為運算式 (如 sal*12)，:NEW 將無法直接對應基礎表欄位，需在觸發器內反向計算。
• 效能考量：觸發器逐列執行，大量 DML (如 INSERT INTO v ... SELECT ... FROM big_table) 會極慢。建議：
  ① 批次操作直接針對基礎表；
  ② 或改用 MERGE + LOG ERRORS；
  ③ 僅在單筆/少量互動式操作使用視圖+觸發器。
• 權限管理：GRANT INSERT/UPDATE/DELETE ON v_emp_dept TO app_user，基礎表 emp/dept 不需直接授權，達到權限最小化。
• 除錯技巧：在觸發器內加入 DBMS_OUTPUT.PUT_LINE 或寫入 DEBUG_LOG 表，記錄 :NEW/:OLD 關鍵值與執行路徑。

替代方案比較

掌握 INSTEAD OF 觸發器的設計模式與限制，能在「資料正規化」與「應用友善介面」之間建立穩健的橋樑。