Re: Mutating problem

In article <3kpo04$935_at_due.unit.no>, toredale_at_pvv.unit.no (Bj|rn Tore Dale) writes:
> I have two tables A and B.
> Table A has a field SUMWGT and STATUS and ARTICLE.
> Table B has a field WGT and LINENO and ARTICLE.
> I would like an Update/Delete Trigger on table B to
> compute the SUM(WGT) for all rows,except the one being updated/deleted
> with the given ARTICLE
> (key on table A is ARTICLE, key on table B is ARTICLE+LINENO)
> and Update the row on table A for this given article with
> a new STATUS, which depend on the new sum.
> The problem is that I get the Oracle error "Table is Mutating",
> even if I make sure that the Where-clause doesn't select
> the row that is being updated/deleted (the error does not, of course,
> occur when inserting).
>
> Can anybody help me out on this one?
> I don't want to call a stored procedure from the client-side to
> perform the Update on table A. I would like to handle all this
> in triggers.
>
> Bjorn T. Dale
> toredale_at_flipper.pvv.unit.no
>
>

I i understand you correctly, you are trying to do something like:

       update A set sumwgt = sumwgt + sum(B.wgt) where ... well the problem is that your sql statement is executed for the first row, then an AFTER ROW trigger is fired which in turn will try to query the current row in Table A, that when you get an error that the table is mutating.
However when you insert, a BEFORE Row trigger fires for a single row insert to table B and can modify any columns in table A, as long as do not violate your integrity constraints.

try to use a temp table, or PL/SQL table.

look in ORALCE server concept manual, chapter 15 for more information about AFTER and before row/statment triggers.

hope this helps.
good luck.

Raghid Ajamoughli
AJAMOUGHLIR_at_beast.cle.ab.com Received on Fri Mar 24 1995 - 15:38:20 CET