Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Error
Mutating Error [message #257398] Wed, 08 August 2007 04:40 Go to next message
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
Re: Mutating Error [message #257408 is a reply to message #257398] Wed, 08 August 2007 05:14 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: Mutating Error [message #257431 is a reply to message #257398] Wed, 08 August 2007 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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.
Re: Mutating Error [message #257457 is a reply to message #257398] Wed, 08 August 2007 07:13 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Or you could create A as a non-updatable view and use an INSTEAD OF INSERT trigger.
Re: Mutating Error [message #257485 is a reply to message #257453] Wed, 08 August 2007 08:18 Go to previous message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi,
Global Temporary table option appears to suit my requirement.

Thank you all for the updates.

Regards,
Previous Topic: ORA-20000: Table not found in db_rolling_partitions_meta
Next Topic: sql query
Goto Forum:
  


Current Time: Sat Dec 03 12:23:56 CST 2016

Total time taken to generate the page: 0.07918 seconds