Re: After Update Trigger

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Mon, 20 Nov 2006 12:08:41 -0500
Message-ID: <4se5opFuuu0tU1@mid.individual.net>

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

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

Received on Mon Nov 20 2006 - 11:08:41 CST