Re: Trigger Question

From: The Magnet <art_at_unsu.com>
Date: Mon, 11 Oct 2010 12:18:38 -0700 (PDT)
Message-ID: <3716d0ed-faee-4f49-966b-9a9819f377f4_at_j25g2000yqa.googlegroups.com>

On Oct 11, 1:49 pm, The Magnet <a..._at_unsu.com> wrote:
> Hi,
>
> We are running 10gR2.
>
> We have a strange issue with our triggers, and it is probably
> something we are missing.  We have a table which has a BEFORE UPDATE
> FOR EACH FOR on it and also an AFTER UPDATE table level trigger on it.
>
> In order to help us debug it we have written out files during the
> execution of the trigger.  These files are the very first thing done.
> You can see the declarations below.
>
> When we run an update statement from SQLPLUS the triggers act normal
> and output those files.  But, when run through the application, the
> BEFORE trigger does not put out any files but the AFTER trigger does,
> which is causing issues.
>
> How can an AFTER trigger fire without firing the BEFORE trigger?  What
> are we missing?
>
> CREATE OR REPLACE TRIGGER ENGINE.order_line_bu_trg
> BEFORE UPDATE ON ENGINE.ORDER_LINE FOR EACH ROW
> DECLARE
>
> v_file1          UTL_FILE.FILE_TYPE;
>
> BEGIN
>    v_file1 := OPEN_FILES('/tmp','before_update.txt','w');
>    UTL_FILE.PUT_LINE(v_file1,'ORDER: ' ||  :NEW.order_id);
>    UTL_FILE.FCLOSE(v_file1);
>    order_suspension.save_order_id := :NEW.order_id;
>
> ======================
>
> CREATE OR REPLACE TRIGGER ENGINE.order_line_au_trg
> AFTER UPDATE ON ENGINE.ORDER_LINE
> DECLARE
>
> v_file2         UTL_FILE.FILE_TYPE;
>
> BEGIN
>   v_file2 := OPEN_FILES('/tmp','after_update.txt','w');
>    UTL_FILE.PUT_LINE(v_file2,'CUSTOMER: ' ||
> order_suspension.save_order_id );
>    UTL_FILE.FCLOSE(v_file2);

You know, developers can sometimes be so DUMB. Imaging, passing a NULL code to the trigger and not coding for that condition.

Problem solved. Received on Mon Oct 11 2010 - 14:18:38 CDT