DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: DB Trigger

  1. #1
    Join Date
    Dec 2005
    Posts
    6

    DB Trigger

    Hi,

    I am trying to create a DB trigger on AR_RECEIVABLE_APPLICATIONS_ALL table to update the custom table when the above table column attribute1 is updated through the form DFF. But I am getting the following error when I save the changes.

    FRM-40735: ON-UPDATE trigger raised unhandled exception ORA-06502.
    --------------------------------------------------------------
    ORA-01403: no data found

    I tested the sql query I have in query and it is giving the value.

    The trigger code is

    CREATE OR REPLACE TRIGGER XXUL_AR_TRE_REFUND_DATE_UPD
    BEFORE UPDATE OF attribute1 ON AR_RECEIVABLE_APPLICATIONS_ALL
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    /*********************************************************************************
    * TYPE : TRIGGER *
    * NAME : XXUL_AR_TRE_REFUND_DATE_UPD *
    * INPUT PARAMETERS : *
    * PURPOSE : This trigger to update the treasury refund date in ULCUS.ULAR_REFUND_PSAP_STAGING
    from dff (attribute1 of AR_RECEIVABLE_APPLICATIONS_ALL)
    DECLARE
    v_tr_refund_date varchar2(150);
    v_receipt_number varchar2(30);
    v_org_id number;
    BEGIN

    begin
    update XX_REFUND_PSAP_STAGING
    set treasury_refunded_date = to_date(:NEW.attribute1,'dd-mon-yyyy')
    where refund_number =
    (select b.RECEIPT_NUMBER
    from AR.AR_RECEIVABLES_TRX_ALL a,
    AR.AR_CASH_RECEIPTS_ALL b,
    AR.AR_RECEIVABLE_APPLICATIONS_ALL c
    where a.org_id = c.org_id and
    a.RECEIVABLES_TRX_ID = c.RECEIVABLES_TRX_ID and
    b.org_id = c.org_id and
    b.cash_receipt_id = c.CASH_RECEIPT_ID and
    c.receivable_application_id = :NEW.receivable_application_id
    AND ROWNUM = 1);
    EXCEPTION
    WHEN OTHERS THEN
    -- FND_MESSAGE.SET_NAME('FND','ERROR:' || substr(sqlerrm,1,200));
    -- FND_MESSAGE.RETRIEVE;
    -- raise_application_error(-20020, 'Its Strange :' || sqlerrm);
    NULL;
    END;



    Any help would be greatly appreciated.

    Thanks in advance.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    The select that is a part of the update is not returning any rows. You need to find out if you wrote the query correctly, and if the data is there when you run this. i.e. are you doing things out of order? Updating data before it gets created? Try to avoid doing "WHEN OTHERS THEN NULL;" And document cases where you intentionally ignore errors.

    Code:
    CREATE OR REPLACE TRIGGER xxul_ar_tre_refund_date_upd
       BEFORE UPDATE OF attribute1
       ON ar_receivable_applications_all
       REFERENCING NEW AS NEW OLD AS OLD
       FOR EACH ROW
    /******************************************************************************
    * TYPE : TRIGGER *
    * NAME : XXUL_AR_TRE_REFUND_DATE_UPD *
    * INPUT PARAMETERS : *
    * PURPOSE : This trigger to update the treasury refund date in ULCUS.ULAR_REFUND_PSAP_STAGING
    * from dff (attribute1 of AR_RECEIVABLE_APPLICATIONS_ALL)
    */
    DECLARE
       v_tr_refund_date   VARCHAR2 ( 150 );
       v_receipt_number   VARCHAR2 ( 30 );
       v_org_id           NUMBER;
    BEGIN
       UPDATE xx_refund_psap_staging
          SET treasury_refunded_date = TO_DATE ( :NEW.attribute1, 'dd-mon-yyyy' )
        WHERE refund_number =
            ( SELECT b.receipt_number
                FROM ar.ar_receivables_trx_all         a,
                     ar.ar_cash_receipts_all           b,
                     ar.ar_receivable_applications_all c
               WHERE a.org_id                    = c.org_id
                 AND a.receivables_trx_id        = c.receivables_trx_id
                 AND b.org_id                    = c.org_id
                 AND b.cash_receipt_id           = c.cash_receipt_id
                 AND c.receivable_application_id = :NEW.receivable_application_id
                 AND ROWNUM = 1 );
    EXCEPTION
       WHEN NO_DATA_FOUND
       THEN
    --    There were no rows to update
          NULL;
    END;
    /

  3. #3
    Join Date
    Dec 2005
    Posts
    6
    I am trying to update the data which is already there. I have attribute1 as '12/12/05' and trying to update it to '12/14/05' throught the form DFF (which is attribute1). If I don't raise the exception, it is saving the data but not updating in Cutom table. Just to test the code I tried all the options with WHEN OTHERS THEN NULL, WHEN OTHERS THEN raise the application error. I can document the errors once it is working ...

    Please help.



    Quote Originally Posted by gandolf989
    The select that is a part of the update is not returning any rows. You need to find out if you wrote the query correctly, and if the data is there when you run this. i.e. are you doing things out of order? Updating data before it gets created? Try to avoid doing "WHEN OTHERS THEN NULL;" And document cases where you intentionally ignore errors.

    Code:
    CREATE OR REPLACE TRIGGER xxul_ar_tre_refund_date_upd
       BEFORE UPDATE OF attribute1
       ON ar_receivable_applications_all
       REFERENCING NEW AS NEW OLD AS OLD
       FOR EACH ROW
    /******************************************************************************
    * TYPE : TRIGGER *
    * NAME : XXUL_AR_TRE_REFUND_DATE_UPD *
    * INPUT PARAMETERS : *
    * PURPOSE : This trigger to update the treasury refund date in ULCUS.ULAR_REFUND_PSAP_STAGING
    * from dff (attribute1 of AR_RECEIVABLE_APPLICATIONS_ALL)
    */
    DECLARE
       v_tr_refund_date   VARCHAR2 ( 150 );
       v_receipt_number   VARCHAR2 ( 30 );
       v_org_id           NUMBER;
    BEGIN
       UPDATE xx_refund_psap_staging
          SET treasury_refunded_date = TO_DATE ( :NEW.attribute1, 'dd-mon-yyyy' )
        WHERE refund_number =
            ( SELECT b.receipt_number
                FROM ar.ar_receivables_trx_all         a,
                     ar.ar_cash_receipts_all           b,
                     ar.ar_receivable_applications_all c
               WHERE a.org_id                    = c.org_id
                 AND a.receivables_trx_id        = c.receivables_trx_id
                 AND b.org_id                    = c.org_id
                 AND b.cash_receipt_id           = c.cash_receipt_id
                 AND c.receivable_application_id = :NEW.receivable_application_id
                 AND ROWNUM = 1 );
    EXCEPTION
       WHEN NO_DATA_FOUND
       THEN
    --    There were no rows to update
          NULL;
    END;
    /

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Is the data in xx_refund_psap_staging being inserted in the same transaction?
    What if you made this an after trigger?

  5. #5
    Join Date
    Dec 2005
    Posts
    6
    yes it is there in xx_refund_staging table. If I run the query outside with the receivable application id hardcoded, it is working fine and also I tried to hardcode the all the values including attribute1 new value and receivablie_application_id in the trigger, still the same error message. So it not the select or update issue.

    I tried the after trigger too ....no luck.

    I found there are already 5 triggers on this table. that could be the issue ?

    Thanks

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You provided the code of DATABASE trigger XXUL_AR_TRE_REFUND_DATE_UPD, but that trigger can never ever raise an "ORA-01403: no data found" error. Not a chance, not the way it is coded - the update statement can never ever raise "no data found", not even when it contains a select statement.

    Your problem is not the database trigger, but your FORMS trigger. You obviously have ON-UPDATATE trigger on that form/block/field (hence the "FRM-40735: ON-UPDATE trigger raised unhandled exception ORA-06502" error message), and that is the trigger that is raising your "no data found" - you must have SELECT INTO clause in that trigger that is not returning any row. That is what you have to sort out.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Dec 2005
    Posts
    6
    That make sense, however, I did not touch the form trigger. Which is Oracle seeded form in AR module. So all I am trying write to DB trigger on that table.

  8. #8
    Join Date
    Dec 2005
    Posts
    6
    If I update the table outside Oracle Apps, it is giving the following error.
    "ORA-04091: table AR.AR_RECEIVABLE_APPLICATIONS_ALL is mutating, trigger/function may not see it"

    Looks like table is mutating ......

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    OK, but "no data found" and "mutating table" are two wery different things! The "mutating table" error is of course caused by the trigger you provided - in the SELECT inside UPDATE statement you are querrying the table that hosts the trigger (AR.AR_RECEIVABLE_APPLICATIONS_ALL), which is not allowed for row level triggers. So that trigger is not causing your "no data found" (for that you'll have to check other triggers on that table), but it surely does cause "mutating table" error.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Dec 2005
    Posts
    6
    Hi Jurij,

    Thanks for the valuable suggestions. I changed the sql statement not use the AR.AR_RECEIVABLE_APPLICATIONS_ALL inside the trigger and it worked fine.

    This issue resolved. thanks once again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width