Home » SQL & PL/SQL » SQL & PL/SQL » [ORA-04091] - Mutating table... Very,.very strange behavior!!!
[ORA-04091] - Mutating table... Very,.very strange behavior!!! [message #217681] Sun, 04 February 2007 11:45 Go to next message
provola74
Messages: 16
Registered: November 2006
Junior Member
Ok let's go with the the strange problem:

1) Login with u working non dba user account.

example: connect student/password@database


2) Let's define:

CREATE TABLE P (NAME VARCHAR2(10) NOT NULL);
CREATE TABLE Q (CNT NUMBER(10) NOT NULL, ID NUMBER(10) NOT NULL);
CREATE TABLE R (MAX NUMBER(10) NOT NULL, ID NUMBER(10) NOT NULL);
INSERT INTO Q VALUES(0,2);
INSERT INTO R VALUES(10,2);


3) While logged in with working account, let's add those:

CREATE OR REPLACE TRIGGER TRG_A
AFTER INSERT OR DELETE ON P 
FOR EACH ROW 
begin
  if deleting then
    update Q set cnt=cnt-1 where id=2;
  else
    update Q set cnt=cnt+1 where id=2;
  end if;
end;

/--- cut point for trigger creation

CREATE OR REPLACE TRIGGER TRG_B
AFTER INSERT OR UPDATE ON Q 
FOR EACH ROW 
declare
 p number;
 maxr number;
begin
  select sum(CNT) into p from Q where id=:new.id;
  select sum(MAX) into maxr from R where id=:new.id;
  dbms_output.put_line('P:'||p||', maxr:'||maxr);
  if (p<=maxr) then
    dbms_output.put_line('p<=maxr is true');
  else
    dbms_output.put_line('p<=maxr is false');
  end if;
end;


4) TRG_A updates the single line of P incrementing or dec. CNT based on the operation that fired the trigger;
5) TRG_B checks if the sum of all CNT in Q are <= MAX and print a message.

6) If u do:

set serveroutput on

begin
  insert into p values('1');
  insert into p values('1');
  insert into p values('1');
  insert into p values('1');
  insert into p values('1');
  insert into p values('1');
  insert into p values('1');
  insert into p values('1');
  insert into p values('1');
  insert into p values('1');
  insert into p values('1');
  insert into p values('1');
  insert into p values('1');
  commit;
end;  


As expected the system will raise an ora-04091 error about mutating tables...

7) But if u now login with the sys dba account:
connect sys/dbapass@database


then drop student.trg_a and student.trg_b and then recreate both under the sys schema:

CREATE OR REPLACE TRIGGER SYS.TRG_A
AFTER INSERT OR DELETE ON STUDENT.P 
FOR EACH ROW 
begin
  if deleting then
    update STUDENT.Q set cnt=cnt-1 where id=2;
  else
    update STUDENT.Q set cnt=cnt+1 where id=2;
  end if;
end;

/--- cut point for trigger creation

CREATE OR REPLACE TRIGGER SYS.TRG_B
AFTER INSERT OR UPDATE ON STUDENT.Q 
FOR EACH ROW 
declare
 p number;
 maxr number;
begin
  select sum(CNT) into p from STUDENT.Q where id=:new.id;
  select sum(MAX) into maxr from STUDENT.R where id=:new.id;
  dbms_output.put_line('P:'||p||', maxr:'||maxr);
  if (p<=maxr) then
    dbms_output.put_line('p<=maxr is true');
  else
    dbms_output.put_line('p<=maxr is false');
  end if;
end;



Cool reconnect with ur working user account:
example: connect student/password@database


9) when re-doing inserts as point 6), the trigger sys.trg_b will fire without raising any ora-04091 error!!!!
The trigger behave like if the mutating table limitation is not existing at all!!!

does anyone know anything about this behavior?
thank u very much
sincerely

PS: I'm sorry for using reserved words like 'max' and so on...this is only an example on what I obtain when doing things...

[Updated on: Mon, 05 February 2007 03:48]

Report message to a moderator

Re: [ORA-04091] - Mutating table... Very,.very strange behavior!!! [message #217694 is a reply to message #217681] Sun, 04 February 2007 20:10 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Are you certain the triggers are compiling successfully? Check the STATUS in DBA_TRIGGERS.

I assume you qualify the table names, such as

AFTER INSERT OR DELETE ON STUDENT.P

Check the dependencies in DBA_DEPENDENCIES to make sure the right triggers are acting on the right tables. Clearly, a trigger on student.p will not mutate against table sys.p


Ross Leishman
Re: [ORA-04091] - Mutating table... Very,.very strange behavior!!! [message #217741 is a reply to message #217694] Mon, 05 February 2007 03:55 Go to previous messageGo to next message
provola74
Messages: 16
Registered: November 2006
Junior Member
Ok, just the original msg was edited to remove dml and others little problems...
..please create the triggers one at time, if u don't in this way the trigger trg_b text will be appended to the trigger trg_a text resulting in copilation errors...
...

rleishman: yes, u have to qualify the names if u remain with sys account even when retrying the inserts...sorry, it was my mistake...(just re-edited the original msg to clearify things)

sincerely

[Updated on: Mon, 05 February 2007 03:58]

Report message to a moderator

Re: [ORA-04091] - Mutating table... Very,.very strange behavior!!! [message #217747 is a reply to message #217741] Mon, 05 February 2007 04:19 Go to previous message
provola74
Messages: 16
Registered: November 2006
Junior Member
while connected as sys dba the:

select * from dba_dependencies where name like '%TRG_B%'; 


reports the attached txt file:

  • Attachment: dep1.txt
    (Size: 4.50KB, Downloaded 138 times)
Previous Topic: concatenate field results
Next Topic: Date filter problem
Goto Forum:
  


Current Time: Mon Dec 05 06:58:06 CST 2016

Total time taken to generate the page: 0.06441 seconds