Re: Trigger - Mutating table - Known problem, but how to solve it ?
From: Frank <fbortel_at_home.nl>
Date: Fri, 03 May 2002 22:00:00 +0200
Message-ID: <3CD2EC40.4090504_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
>>>
>>>
>>>
>>Search the archives...
>>Hint: Use a row-level trigger to identify
>>what to do, and a statement trigger to do it.
>>
Date: Fri, 03 May 2002 22:00:00 +0200
Message-ID: <3CD2EC40.4090504_at_home.nl>
Alex Filonov wrote:
> 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.
>>
Yup - solves the problem in a very elegant way, but was not an answer to the question. Which was wrong, too. You anwered the question that should have been asked ;-) Received on Fri May 03 2002 - 22:00:00 CEST