OraFAQ Forum: SQL & PL/SQL » Mutating Error

Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Mutating Error [message #257398]

Wed, 08 August 2007 04:40

sandhyaa
Messages: 79
Registered: February 2007

Member

Hi,

I have two tables tableA and tableB.

Rows will be inserted into tableA (using some excel import utility) , i am inserting the same values to tableB inside the row-level trigger on tableA. Soon after the rows are inserted into tableB, I wanted it to be deleted from tableA.

I have written two triggers on tableA

CREATE OR REPLACE TRIGGER tableA_BRI  BEFORE INSERT
 ON tableA FOR EACH ROW
BEGIN
   --Do some validations on the data that is inserted

END;

CREATE OR REPLACE TRIGGER tableA_ARI  AFTER INSERT
 ON tableA FOR EACH ROW
BEGIN
  --sql to insert rows into tableB 

END

If I place a delete tableA statement in the AFTER INSERT Trigger, it gives ORA-04091 mutating error. So how do I accomplish. The intention is that soon after rows are inserted into tableB, all rows in tableA should be deleted.

Thanks
Sandi

Report message to a moderator

Re: Mutating Error [message #257408 is a reply to message #257398]

Wed, 08 August 2007 05:14

Maaher
Messages: 7065
Registered: December 2001

Senior Member

The functionality as you describe it is as follows:
- user performs an insert into tableA
- trigger inserts into tableB and deletes tableA

So, in short: you only insert into tableB

You have a few options:
- Insert directly in tableB and don't use tableA
- Don't do it in a trigger. Use a procedure instead.
- Use the standard workaround as it is found here on the board and on asktom.oracle.com.

MHE

Report message to a moderator

Re: Mutating Error [message #257431 is a reply to message #257398]

Wed, 08 August 2007 06:27

Michel Cadot
Messages: 68723
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

drop table a;
create synonym a for b;

Regards
Michel

[Updated on: Wed, 08 August 2007 07:16]

Report message to a moderator

Re: Mutating Error [message #257453 is a reply to message #257431]

Wed, 08 August 2007 07:07

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

A couple more options:

Create A as an updatable view on table B.

Create table A as a Global Temporary table with a session duration, and have an After Insert trigger to insert rows into B.
The rows in A will go as soon as the session inserting into A ends.

Report message to a moderator