Re: Mutating table

On 10 Mar 1998 14:51:31 GMT, bagnonm_at_I
>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.

Oracle Press's PL/SQL 7/8 book has an excellent "work around". (It will also define a mutating table. See rule number 2 because it is the one you are hitting on) Use a before insert trigger on the detail table to set a package global variable to the PK billlineid. Do the update to the master table in an AFTER INSERT (and/or UPDATE) trigger which uses the packaged global variable. Received on Fri Mar 13 1998 - 00:00:00 CST