This discussion is archived
7 Replies Latest reply: Apr 1, 2013 4:52 AM by jeneesh RSS

oracle trigger - return control after procedure starts

marco Newbie
Currently Being Moderated
Hi all,

I've got table table1 with trigger trigger1
CREATE or REPLACE TRIGGER trigger1
AFTER UPDATE
   ON table1

BEGIN
   procedure1;

EXCEPTION
   WHEN OTHERS THEN
    return;

END;
It works fine, I mean it starts procedure procedure1 after performing update on table table1.

The question is, is it normal behavior that my update script waits untill procedure procedure1 runs? I don't have experience with triggers yet. I'm looking for some way to return control just after procedure procedure1 starts, I don't want to wait untill it ends its work.
Is it possible at all?

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
  • 1. Re: oracle trigger - return control after procedure starts
    Ramin Hashimzadeh Expert
    Currently Being Moderated
    can you tell us, wtha is your procedure "procedure1;" doing? I think you don't need trigger. Also you can run procedure1; at the end of update script;
  • 2. Re: oracle trigger - return control after procedure starts
    jeneesh Guru
    Currently Being Moderated
    marco wrote:
    Hi all,

    I've got table table1 with trigger trigger1
    CREATE or REPLACE TRIGGER trigger1
    AFTER UPDATE
    ON table1
    
    BEGIN
    procedure1;
    
    EXCEPTION
    WHEN OTHERS THEN
    return;
    
    END;
    It works fine, I mean it starts procedure procedure1 after performing update on table table1.

    The question is, is it normal behavior that my update script waits untill procedure procedure1 runs?
    That is very normal, and that is what trigger supposed to do..
    The abnormal thing is your exception part. WHEN OTHERS THEN without a RAISE is considered as a BUG. You will never come to know about the unexpected errors happening in your trigger...
    I don't have experience with triggers yet. I'm looking for some way to return control just after procedure procedure1 starts, I don't want to wait untill it ends its work.
    Is it possible at all?
    You are asking for a Background job - The only way is to submit a job using the trigger - That is NOT AT ALL advisable..Better wait for the procedure to complete..

    >
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
  • 3. Re: oracle trigger - return control after procedure starts
    997808 Newbie
    Currently Being Moderated
    The control cannot be returned until the procedure 1 completes.
    You can run the procedure after the update script directly. I think the trigger is not needed in this case
  • 4. Re: oracle trigger - return control after procedure starts
    marco Newbie
    Currently Being Moderated
    can you tell us, wtha is your procedure "procedure1;" doing? I think you don't need trigger. Also you can run procedure1; at the end of update script;
    This is part of main procedure procedure_main. It inserts data into tables t1@dblink, t2@dblink, ... tN@dblink.
    After all inserts are complete I perform update table1@dblink.

    Procedure procedure1 mentioned earlier runs aggregation of data @ remote server. This is important, because I can't waste time and machine resources at local server to aggregate data at remote server.

    So I'm looking for some way to start procedure1 @ remote server when data inserting is complete. It works fine. Except this little thing that control is not returned. I do perform other works during the night and don't want to wait untill aggregation is complete.
    Any advises?

    Edited by: marco on 1/4/2013 2:38
  • 5. Re: oracle trigger - return control after procedure starts
    jeneesh Guru
    Currently Being Moderated
    marco wrote:
    can you tell us, wtha is your procedure "procedure1;" doing? I think you don't need trigger. Also you can run procedure1; at the end of update script;
    This is part of main procedure procedure_main. It inserts data into tables t1@dblink, t2@dblink, ... tN@dblink.
    After all inserts are complete I perform update table1@dblink.

    Procedure procedure1 mentioned earlier runs aggregation of data @ remote server. This is important, because I can't waste time and machine resources at local server to aggregate data at remote server.

    So I'm looking for some way to start procedure1 @ remote server when data inserting is complete. It works fine. Except this little thing that control is not returned. I do perform other works during the night and don't want to wait untill aggregation is complete.
    Any advises?

    Edited by: marco on 1/4/2013 2:38
    In this case I Would suggest to have a FLAG table in the remote db. Instead of doing a lot of things in the remote DB, let the trigger insert a flag entry to the remote FLAG table. And let the remote db have a scheduled job (with the required frequency) to check the flag entry and run the procedure from there itself..
  • 6. Re: oracle trigger - return control after procedure starts
    marco Newbie
    Currently Being Moderated
    You are asking for a Background job - The only way is to submit a job using the trigger - That is NOT AT ALL advisable.
    Could you please be so kind to provide more details? 'Cause I don't understand the reason why that is not at all advisable.
  • 7. Re: oracle trigger - return control after procedure starts
    jeneesh Guru
    Currently Being Moderated
    What if the procedure running in the JOB failed?

    Do you want to rollback the DML you did?

    If you have a FLAG table as suggested, you could control the procedure flow from the remote DB.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points