user2811127 wrote:How do you know that the trigger is not firing? Couldn't it be firing, but failing to write to the log table?
I have a before trigger on a Oracle 11g table that allows me to audit the table to see who is changing data by saving the old value in a variable and logging it along with the new variable and more data into a table. My issue is sometimes_ the trigger does not fire when it meets the criteria.
Does anyone have any idea why this would happen or not happen?Not without seeing the trigger.
user2811127 wrote:Why do you say that?
It doesn't appear to be mutating.
then anything that I do to the table other than an INSERT ... VALUES (which is a special case where Oracle knows in advance that only one row is changing) will throw a mutating table exception
SQL> drop table a; Table dropped. SQL> create table a( col1 number, col2 date ); Table created. SQL> ed Wrote file afiedt.buf 1 create or replace trigger mutating_trigger 2 before insert or update on a 3 for each row 4 declare 5 l_old_col1 number; 6 begin 7 begin 8 select col1 9 into l_old_col1 10 from a 11 where col2 = (select max(col2) 12 from a 13 where col2 < :new.col2); 14 exception 15 when no_data_found 16 then 17 l_old_col1 := -1; 18 end; 19 dbms_output.put_line( 'Old col1 = ' || l_old_col1 ); 20* end; SQL> / Trigger created.
Are you saying that this is not what you're seeing? Are you certain that you have removed all the WHEN OTHERS exception handlers that may be hiding the error?
SQL> insert into a values( 1, sysdate ); Old col1 = -1 1 row created. SQL> insert into a values( 2, sysdate ); Old col1 = 1 1 row created. SQL> update a 2 set col1 = col1 + 1; update a * ERROR at line 1: ORA-04091: table SCOTT.A is mutating, trigger/function may not see it ORA-06512: at "SCOTT.MUTATING_TRIGGER", line 5 ORA-04088: error during execution of trigger 'SCOTT.MUTATING_TRIGGER' SQL> ed Wrote file afiedt.buf 1 insert into a 2 select 4, sysdate 3* from dual 4 / insert into a * ERROR at line 1: ORA-04091: table SCOTT.A is mutating, trigger/function may not see it ORA-06512: at "SCOTT.MUTATING_TRIGGER", line 5 ORA-04088: error during execution of trigger 'SCOTT.MUTATING_TRIGGER'