Re: Help on Mutating

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 27 Mar 1999 01:38:40 GMT
Message-ID: <36fe35f4.3663127@192.86.155.100>

A copy of this was sent to tkyte_at_us.oracle.com (Thomas Kyte) (if that email address didn't require changing) On Fri, 26 Mar 1999 20:05:35 GMT, you wrote:

>A copy of this was sent to kelly <mekelly002_at_hotmail.com>
>(if that email address didn't require changing)
>On Fri, 26 Mar 1999 12:46:07 -0500, you wrote:
>
>>Question as following:
>>
>>From Oracle8 document:
>> BEFORE ROW and AFTER ROW triggers fired by a single row INSERT to a
>>table do
>> not treat that table as mutating or constraining. Note that INSERT
>>statements that may involve more than one row, such
>> as INSERT INTO emp SELECT . . ., are not considered single row
>>inserts, even if they only result in one row being
>> inserted.
>>

While my other post would work -- in hindsight, its way over engineered given the tables as defined. A much simplier solution is a trigger:

create or replace trigger po_2_dtl_aiudfer after insert or update or delete on po_2_dtl for each row begin

        if (inserting or updating ) then
            update po_2
               set x_amt = x_amt + nvl(:new.ordr_qty*:new.ordr_prc,0)
             where po_id = :new.po_id;
        end if;
        if (updating or deleting ) then
            update po_2
               set x_amt = x_amt - nvl(:old.ordr_qty*:old.ordr_prc,0)
             where po_id = :old.po_id;
        end if;

end;
/

It will work just the same with less code... (unless you actually have declaritive integrity on the parent/child relationship in which case my original post is the 'correct' answer -- it avoids the mutating table error you would get updating the po_2 table)...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Mar 26 1999 - 19:38:40 CST