Oracle Trigger 是個方便但是不易管理的工具，所以資料庫前輩們總會諄諄教誨著，基本上能不用就不用。但是最近一個問題案例，思來想去，沒有比 Oracle Trigger 更適合的解決方法。然而這個被經驗法則所封印起來的技術，在實作的過程中，也遇到一些有趣的問題。- 案例簡述與建立 Oracle Trigger
有一個原本已被排程執行，用以更新資料的 Stored Procedure，在某個 Table 裡的某個 Column 的資料被更動的時候，也需要即時被執行以反應該欄位資料變動所帶來的相關影響。
為達成此目的，阿布建立了一個如下所示的 Oracle Trigger：
這個 Oracle Trigger 的動作簡單描述就是，在 Table tbl 的欄位 colYNSwitch 的資料被由 'N' UPDATE 為 'Y' 的時候，觸發 Oracle Trigger，傳入參數 referKey 來執行 Stored Procedure pc_for_update_data。十分直覺而且簡單吧！
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 本身的動作在大部分情況下，即可滿足這樣子案例的需求。但是，在實作上，被呼叫執行的 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。- 建立中介的 package 以 global variable 方式來存放 referKey：
CREATE OR REPLACE PACKAGE pg_variables AS
v_referKey tbl.referKey%TYPE;
END; - 建立 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; - 建立 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 的資料型態的宣告與指派的差異。 - 建立中介的 package 以 global variable 方式來存放 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
繼續閱讀
每每在資料庫的設計與應用上有點小小小小小小的功績的時候，總不由得會讓阿布回憶起那位阿布在資料庫方面的啟蒙老師 － KV先生。就來說說，這段奠定日後根基也影響深遠的貴人故事…
Blogger 設定 RSS Feed 的地方在後台介面的「設定 > 網站提供」的頁籤可以設定調整。但是，它也只有兩種選項可以選：完整，簡短。用「完整」呢…訂閱的讀者在 Reader 或 E-Mail 就可以看完全文，流量帶不進來…而「簡短」又往往被系統斷在很隨性的地方…搞不好引言就這樣變狗屁不通不夠引人入勝，流量也帶不進來…所以…Blogger 的 RSS Feed 是否能夠自訂引言，然後一個「繼續閱讀」把訪客帶進來，讓精心的排版不會淪為白工，要怎麼做呢？
最高等級提昇至LV32，總經驗值需500000。餐廳可在兩邊各擴充一個面積單位，及九塊食材田地開啟。
該來的還是躲不過，以遊戲開發商來說，有一款人氣遊戲，怎不會想把它變金雞母？Playfish Cash 就這樣以一種很骯髒的方式進駐了 － 每種食材也都能隨選購買了。這遊戲，除了餐廳的裝潢，動線的規劃以外，好玩就好玩在食材的儲備與菜色的升級。
雞肋到不行的更新，每天可以送『食物』給八個朋友。
這次的更新除了開放全螢幕支援，很大部分都是在互動的強化。玩家可以用滑鼠點擊的方式清理馬桶及修理電動機台，更妙的是服務生忙不過來還能幫忙收盤子。這三個項目都設立了新的獎盃。
訂閱 (SUBSCRIBE)
