Re: Mutating table

On 10 Mar 1998 14:51:31 GMT, bagnonm_at_I_should_put_my_domain_in_etc_NNTP_INEWS_DOMAIN () wrote: Hi,

try something like:

l_Total as Number
Begin

	Select 
		sum(billinetotal) into l_total
	from
		billine
	where
		billid = :new.billid
	and
		billineid != :new.billineid
	;					
	l_Total := l_total + :new.billinetotal;
	Update
		bill
	Set 
		billtotal = l_Total
	where
		billid = :new.billid
	;

You've got to exclude the new record from sum() otherwise you'll get table is mutating.

>Suppose you have a master-detail schema. Let the master be
>
>TABLE bill
> billid NUMBER
> ...
> billtotal NUMBER
> PRIMARY KEY billid
>
>The detail table holds the bill lines, something like
>
>TABLE billline
> billid NUMBER
> billlineid NUMBER
> billarticle NUMBER
> billlinetotal NUMBER
> PRIMARY KEY billlineid
> FOREIGN KEY billarticle REFERENCES articles(artid)
> FOREIGN KEY billid REFERENCES bill(billid)
>
>In the master table you want to have the
>sum of all the <billlinetotal>
>fields of the detail lines.
>
>When I set up a trigger on insert of
>the detail table, to update the master
>field, I get a MUTATING TABLE error.
>
>So, the question is:
>
>How can I update fields of
>the master table when inserting
>detail table rows?
>
>Thanks.
>--
>
> 8^) bagnonm_at_inta.es 8^)
>
>Oh, I am a C programmer and I'm okay
> I muck with indices and structs all day
>And when it works, I shout hoo-ray
> Oh, I am a C programmer and I'm okay
>

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de