2010-03-01

用 Oracle 觸發器呼叫預儲程序 (Calling Stored Procedure via Trigger)

阿布洛格 OracleOracle Trigger 是個方便但是不易管理的工具,所以資料庫前輩們總會諄諄教誨著,基本上能不用就不用。但是最近一個問題案例,思來想去,沒有比 Oracle Trigger 更適合的解決方法。然而這個被經驗法則所封印起來的技術,在實作的過程中,也遇到一些有趣的問題。


  • 案例簡述與建立 Oracle Trigger
    有一個原本已被排程執行,用以更新資料的 Stored Procedure,在某個 Table 裡的某個 Column 的資料被更動的時候,也需要即時被執行以反應該欄位資料變動所帶來的相關影響。

    為達成此目的,阿布建立了一個如下所示的 Oracle Trigger:
    CREATE OR REPLACE TRIGGER tbl_trg
    BEFORE UPDATE ON tbl
    FOR EACH ROW
    BEGIN
    IF :old.colYNSwitch = 'N' AND :new.colYNSwitch = 'Y' THEN
    pc_for_update_data(:new.referKey);
    END IF;
    END;
    這個 Oracle Trigger 的動作簡單描述就是,在 Table tbl 的欄位 colYNSwitch 的資料被由 'N' UPDATE 為 'Y' 的時候,觸發 Oracle Trigger,傳入參數 referKey 來執行 Stored Procedure pc_for_update_data。十分直覺而且簡單吧!

    雖然這個 Oracle Trigger 本身的動作在大部分情況下,即可滿足這樣子案例的需求。但是,在實作上,被呼叫執行的 Stored Procedure 裡頭所進行的動作,卻可能讓 Oracle Trigger 在 compile 的時候跳出錯誤。


  • ORA-04091 錯誤: table is mutating, trigger/function may not see it.
    會出現這個錯誤,是因為被 Trigger 所呼叫的 Procedure 裡頭有使用到正在被更新的資料表。而因為 Trigger tbl_trg 的觸發/執行時機是 BEFORE UPDATE ,當下對資料表 tbl 所進行的變更尚未進行 COMMIT,所以如果此時預儲程序 pc_for_update_data 如果去參照/使用資料表 tbl,就會觸發 ORA-04091 錯誤: table is mutating, trigger/function may not see it。

    要避免這樣的錯誤,原本一步到位的動作需要稍加拆解:先知道哪(些)筆資料被更新了,等到資料表 tbl 完成了更新,再針對那(些)筆資料來呼叫執行預儲程序 pc_for_update_data。
    1. 建立中介的 package 以 global variable 方式來存放 referKey:
      CREATE OR REPLACE PACKAGE pg_variables
      AS
      v_referKey tbl.referKey%TYPE;
      END;

    2. 建立 Oracle Trigger 在資料變更前(BEFORE UPDATE),先將 referKey 暫存至之前建立的 global variable。
      CREATE OR REPLACE TRIGGER trg_before_update
      BEFORE UPDATE ON tbl
      FOR EACH ROW
      BEGIN
      IF :old.colYNSwitch = 'N' AND :new.colYNSwitch = 'Y' THEN
      PG_VARIABLES.v_referKey := :new.referKey;
      ELSE
      PG_VARIABLES.v_referKey := NULL;
      END IF;
      END;

    3. 建立 Oracle Trigger 在資料變更後(AFTER UPDATE),傳入前一個 Trigger 寄放/暫存的 global variable 作為參數來執行預儲程序 pc_for_update_data。
      CREATE OR REPLACE TRIGGER trg_after_update
      AFTER UPDATE ON tbl
      BEGIN
      IF PG_VARIABLES.v_referKey IS NOT NULL THEN
      pc_for_update_data(PG_VARIABLES.v_referKey);
      END IF;
      END;
    到這,ORA-04091 錯誤應該已經順利解決。幸運的是,這個案例遇到的資料更新都是單筆的情況,相對單純。多筆的話,應該也只是 PG_VARIABLES.v_referKey 的資料型態的宣告與指派的差異。
  • ORA-04092 錯誤: cannot [ROLLBACK|COMMIT|SET SAVEPOINT] in a trigger.
    過了一關還有一關。會出現這個錯誤,是因為 Trigger 所呼叫執行的 Stored Procedure 裡有包含 TCL(Transaction Control Language),舉凡 ROLLBACK,COMMIT,SAVEPOINT 等,都會觸發此錯誤。

    這是資料庫架構上的概念了,簡單來說,Trigger 的觸發是 Transaction 中的一部份,而 TCL 則使用於 Transaction 與 Transaction 之間,所以,Trigger 或 Trigger 所呼叫執行的預儲程序中,不可含有 TCL 是合理的。有興趣可以看這裡

    『沒有功勞就只有疲勞』的道理一樣,解決不了問題,再多的合理都是不合理的』

    前面提過,需要執行的 Stored Procedure 是一個原本已被排程執行,用以更新資料的預儲程序。原則上,不太可能(也不太願意)因此去調整既有的流程及資料流架構。要是在盤根錯節的系統,聽到架構的問題,可能就讓人眼前一黑,一生如走馬燈般閃過腦海。但是,ORA-04092 錯誤的解決方法,其實出乎想像地簡單,輕輕加上一句以下的「咒語」
    PRAGMA AUTONOMOUS_TRANSACTION;
    放置在上面提到的 trg_after_update 就成了以下的模樣:
    CREATE OR REPLACE TRIGGER trg_after_update
    AFTER UPDATE ON tbl
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
    IF PG_VARIABLES.v_referKey IS NOT NULL THEN
    pc_for_update_data(PG_VARIABLES.v_referKey);
    END IF;
    END;

    這樣,雖然解決了 ORA-04092 錯誤,但是畢竟還是用了不合理的方式解決了合理的錯誤,所以還是請帶著一顆愧疚的心,在心裡暗暗發誓:『有朝一日,還是要以合理的方式解決架構上不合理的問題。(遠目)』

    還真是繞舌…= =a

0 回應 :

張貼留言

讓阿布知道你對這篇文章的想法吧!