Serge Rielau wrote:
> DA Morgan wrote:
>> Mikail Dellovich wrote:
>>> DA Morgan wrote:
>>>> Mikail Dellovich wrote:
>>>>> How may I setup a trigger on a table being updated to update
>>>>> another table based on the current table being updated with out
>>>>> getting the mutating table error. Is there a non row after update
>>>>> trigger for the whole table the triggers after all the rows are
>>>>> updated?
>>>>>
>>>>> Thanks
>>>>
>>>> By posting your database version (the rules are version specific)
>>>> By posting your trigger code so we can see what you are trying to do
>>>> By posting the actual error message you are receiving
>>>
>>> Database: 10gr2
>>>
>>> I will give you an example:
>>>
>>> table a
>>> (
>>> col1 varchar2(20),
>>> col2 number(20)
>>> )
>>>
>>> table b
>>> (
>>> col1 varchar2(20),
>>> col2 number(20)
>>> )
>>>
>>> procedure a(lvcol)
>>> {
>>>
>>> select sum(col2) into lvsum from table a where col1=lvcol;
>>>
>>> update table b col2=lvsum where col1=lvcol;
>>> }
>>>
>>>
>>> I want to call procedure a everytime table a is updated or inserted.
>>>
>>> Thanks
>>
>> 1/2 way there but you still haven't posted the code causing the
>> error. Without that all we can do is guess as to what you are
>> doing wrong.
>>
>> But as generic advice, which might well be bad advice given I still
>> don't know what you are doing, make the procedure an AUTONOMOUS
>> TRANSACTION.
> Actually that would, I think, cause a deadlock (or at least a timeout).
> Note that the example looks like it will include the rows updated by the
> trigger. That aside it would leave wrong c2 values in case of a rollback.
>
> The way I understand it the OP tries to maintain a derived column
> SUM(col2) OVER(PARTITION BY col1) within the table.
> That is any time one of the rows in the group is changed all others need
> to be updated.
> Questionable design.... Perhaps reading up on materialized views would
> be in order.
>
> Cheers
> Serge
Very questionable design. I think my caveat: "which might well be bad
advice" expresses my feeling about my suggestion. AUTONOMOUS
TRANSACTIONS are often the solution to mutating trigger issues. That
doesn't mean they are the best solution or in this case even a useful
solution.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Nov 20 2006 - 12:09:03 CST
