2008/03/18

[Oracle][KB]ORA-04091 table string.string is mutating, trigger/function may not see it

第一次寫 Oracle Trigger 就遇到這種怪問題,其實 Trigger 寫的也很簡單

這是 Sample 的 Trigger

   1:   
   2:   
   3:  select count(*) into v_cnt from tableA where xxxx
   4:   
   5:  if (v_cnt>0) then
   6:   
   7:  ...........
   8:   
   9:  end;

 

結果遇到 TableA 的 Trigger 要再去存取 TableA 時,會出現以下錯誤訊息

 

ORA-04091 table string.string is mutating, trigger/function may not see it

Cause: A trigger (or a user defined PL/SQL function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.

Action: Rewrite the trigger (or function) so it does not read that table.

 

後來找到在 Trigger 使用 pragma autonomous_transaction 可以避免一些 Transaction 問題,

加上去後,果然是可以順利的觸發 Trigger , Thanks Google.

 

另外在補充一點,不只是 Trigger 可以使用  ,例如像自行定義的給號 Oracle Function 也可以加上去,

可以讓很單純的給號程式,可以直接在 Insert 指令做完,例如

Select GetSeqno('活動序號')  from dual
Insert Into Actioin (Seqno,ActionName) values ( GetSeqno('活動序號'),'到訪1萬人次活動');
 

沒有留言: