Mutating Table issue

[froth]

I have been trying to solve an issue where I need to capture a new record in one table on insert and create another row in the same table with some of the data from the inserted record.

For example 4 fields of data from the inserted record will be stored in a new record in the same table. I also need a trigger that will run on delete where a row is deleted, another row in the same table will be deleted based upon data within the row that was orignally deleted and caused my trigger to fire.

I keep running into the mutating error when I try to build this out and cannot seem to find a way around it. Any suggestions or examples?

Thanks

[PAVB]

Are you getting ORA-04091?

oerr ORA 04091
04091, 00000, "table %s.%s is mutating, trigger/function may not see it"
// *Cause: A trigger (or a user defined plsql 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.

http://asktom.oracle.com/tkyte/Mutate/index.html

[froth]

I did try this but for some reason with all 3 triggers I now get no errors but also no new row inserted into the table other than the orignal which should have cause the trigger to fire.

So I am at a loss.

I would like that if table A has a record inserted, values from column 1,2,3,4,5 are used with a new insert into the same table (Table A).

[ssu4716]

why do you need to insert the values into the same table? it does seem like a bad design to me.

may be you should try to insert the values into another table.

[PAVB]

why do you need to insert the values into the same table? it does seem like a bad design to me.

may be you should try to insert the values into another table.

That would certainly improve the sloppy design LOL

[TimHall]

Hi.

I have to agree that it sounds like bad design, but here's a method for dealing with that error if you have no other way out:

http://www.oracle-base.com/articles/...Exceptions.php

Cheers

Tim...

[gamyers]

I would like that if table A has a record inserted, values from column 1,2,3,4,5 are used with a new insert into the same table (Table A).

But that triggered insert is also an insert into table A, so would fire the trigger, resulting in another insert ad infinitum....Unless you have shares in a company making disk drives, this doesn't seem like a good idea.

How about you tell us what the APPLICATION is trying to achieve.