Re: Trigger question

On 15 Apr 1997 16:41:50 GMT, jim_at_jpcr.com (Jim Pistrang) wrote:

>Oracle experts, please help!
>
>I've got an Oracle Trigger that kicks in on an update to a 'child' table.
>It passes the the parent key to an Oracle Stored Procedure, which sets a
>flag in the parent record based on a calculation involving sum() amounts
>in the children.
>
>Here's the trigger:
>
>CREATE OR REPLACE TRIGGER TRIG_PO_OPEN
>AFTER UPDATE ON PO_LINE
>FOR EACH ROW
>begin
>SP_PO_OPEN(:new.po_id);
>end;
>
>and here's the Stored Procedure:
>
>CREATE OR REPLACE PROCEDURE SP_PO_OPEN (poid IN NUMBER)
>AS
>sum1 NUMBER;
>sum2 NUMBER;
>begin
>select sum(qty_made) into sum1 from po_line where po_id = poid;
>select sum(qty_rec) into sum2 from po_line where po_id = poid;
>IF sum1-sum2=0 and sum1>0 THEN
> update po set po_open = 1
> where po_id = poid;
>END IF;
>IF sum1-sum2<>0 THEN
> update po set po_open = 0
> where po_id = poid;
>END IF;
>end;
>
>If I simply call the Procedure passing a key, it works fine. But when I
>enable the trigger and update the PO_LINE table I get an error saying
>'table is mutating, trigger/function may not see it...'. It seems to be
>saying that I can't do a sum() in a Stored Procedure on a record that's
>being updated. I thought I could do this sort of thing...what am I doing
>wrong?
>
>Jim

Jim,

        You hit the nail right on the head. The row that you are updating is still an 'in-doubt' transaction, ie uncommitted. Since you are in the process of changing information on that row, the execution of the trigger could only look at the committed data, and the trigger cannot 'see' the data that you have modified since you have not committed it yet. Remember, Oracle assumes that you can rollback any transaction you start, so if you roll it back you might introduce inconsistencies in your data, and Oracle does not allow this condition to happen. Refer to your Oracle 7 Server Concepts Manual for more information.

Alan Caldera
Sr. Programmer/Analyst
Michaels Stores, Inc.
acaldera_at_airmail.net Received on Wed Apr 16 1997 - 00:00:00 CDT