Re: Trigger - Mutating table - Known problem, but how to solve it ?

Frank <fbortel_at_home.nl> wrote in message news:<3CCEE4BD.2000109_at_home.nl>...
> hompie wrote:
>
> > Hi,
> >
> > I have a mutating table problem with a trigger. Question is, how is
> > this solved most of the time ?
> > I basically have a BOM table, with an article, it's master, and a
> > level. What i want to do is, if i add a level, cascade all the other
> > dependent ones to go down one level. This is the goal.
> >
> > TableA
> >
> > ArticleID MasterID Level
> >
> > 10 9 5
> > 11 9 5
> > 12 11 6 => update
> > 13 12 7
> >

The problem doesn't exist. You don't have to store level in the table. Create a view based on connect by query and use level pseudocolumn to represent the level you want. You don't need a trigger in this case at all.

The best code solution is no code at all.

> > Say i add a lineitem on article 12. Logically. Article 13, which has
> > 12 as parent,; has to go from level 7 to 8 . Problem is. Any type of
> > trigger seems to cause the mutating table error. I cannot update this
> > table with the new level as the table is mutating.
> > So what do I have to do to solve this ? the update is required, i do
> > not want the application code to do this, it should be done on oracle
> > level, as it causes a cascade.
> > Do i put this value in another table and do it afterwards ?
> >
> > Any tips are appreciated. I know how to code, just don't know what to
> > code in this case
> >
> > best regards,
> > Steve
> >
>
> Search the archives...
> Hint: Use a row-level trigger to identify
> what to do, and a statement trigger to do it.
Received on Wed May 01 2002 - 17:17:55 CEST