| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Mutating table problem + open cursors
Sounds like a recursion issue in the trigger (the trigger is causing itself to fire over and over again) but without the code, we can't tell.
If you are trying to implement a cascade delete, have you considered:
create table emp
( empno number primary key,
mgr number references emp(empno) on delete cascade
)
/
Can you let the database do the delete cascade?....
On Tue, 11 Feb 1997 16:05:29 -0600, Henri van den Bulk <hvdbulk_at_lgc.com> wrote:
>Hi,
>
>We have been having problems with mutating table errors. The senario is
>that we have a table
>that contains parent-child relatied rows. For example row 1 can be the
>parent for row 3. The entity is such that we need this relationship back
>to the same entity.
>
>To inforce RI in the db we create a trigger that on delete of row would
>delete all the child rows in the same entity. Of course this cause a
>mutating table error.
>Now I found an article that advised to write a trigger that records the
>fact that a row
>has been deleted (after delete for each row trigger). This trigger
>writes the the SK into a temp. table. Then after the delete (after
>delete on entiry trigger) an other trigger fires and deletes the child
>rows that are assosiated to the rows that have been recorded in the
>temp. table.
>
>The problem that we're seeing now is that the db. generates an error
>stating that it has exceeded the max open cursors "ORA-01000: maximum
>open cursors exceeded". This is
>weird because it is set to 150 in init.ora and there are only 6 rows in
>the database.
>
>Any suggestions?
>
>--
> Henri M.B. van den Bulk
> Landmark Graphics Corp.,
> Drilling & Well Services Product Group
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
 |
 |