Re: Committing from a Forms 4.0 trigger

Peter Walve (sctwalve_at_kraken.itc.gu.edu.au) wrote:
: Hello,
 

: I'm hoping someone can help me out with this annoying problem:
 

: Sample trigger on when-button-pressed in Forms 4.0..
 

: --------
: BEGIN
 
: IF :status = 'OK' THEN
: UPDATE othertable set status = :status where id = :id;
: COMMIT;
 
: end if;
: EXCEPTION
: WHEN NO_DATA_FOUND THEN
: INSERT INTO OTHERTABLE (etc.....);
: COMMIT;
: END;
 
: --------
 

: If I exclude the COMMIT; lines, the othertable is not updated immediately.
:
: If I include COMMIT; the othertable is updated but a message 'no changes to
: commit' appears. This is confusing to form users.
 

: Q. Is there any way to immediately modify an unrelated table from a
: trigger within Forms 4, either by a) hiding the annoying message,
: or b) committing only the change to the
: othertable, not the whole form?
: One way is to have a hidden dummy bind variable changing for each button
: press, is there anything easier?
:
: Many Thanks for any advice.
 

: Peter Walve
: QPRI.
:

Peter,

If you only want to suppress the message you can do so by setting SYSTEM.MESSAGE_LEVEL := 5 before the commit and setting it back to zero afterward. If you want to commit the update statement without committing other changes in the form then a solution is to have your trigger call a stored procedure which does the update and then commits. As long you have no 'posted' transactions that were generated by the form then you should be okay.

By the way, your NO_DATA_FOUND exception handler will not fire if the update statement fails to update any rows. Instead of using an exception try:

    update .......;

    if SQL%NOTFOUND then

        insert into ....;
    end if;

Hope this helps,

Chris Cummings,
Dallas Softworks, Inc. Received on Mon Feb 06 1995 - 20:04:37 CET