-
Mutating Table issue
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
-
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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).
-
bad design?
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.
-
Originally Posted by 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.
That would certainly improve the sloppy design LOL
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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...
-
Originally Posted by froth
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|