This discussion is archived
2 Replies Latest reply: Feb 15, 2013 9:12 AM by user346369 RSS

key-commit conflict with on-error

Maahjoor Newbie
Currently Being Moderated
Dear all,
i am using forms 6i with database 10g.
i have defined 2 triggers at the form level

key-commit
on-error

my key-commit trigger is below
begin
     if :system.form_status='CHANGED' THEN
commit_form;
if form_success then
     clear_message;
     message('Record is saved sucessfully.');message(' ',no_acknowledge);
else
     message('Error: Record is not saved.check your data or contact DBA.');
raise form_trigger_failure;
end if;
ELSE NULL;
     END IF;
end;
now, when i do some changes in the runtime, and press save button, it give me error which i handled in the on-error trigger, the record is not saved,
but i got message 'Record is saved sucessfully'.
why? i said in the key-commit that if form_success then do succeeding message.

any idea?

Edited by: Maahjoor on Feb 15, 2013 4:30 AM
  • 1. Re: key-commit conflict with on-error
    Maahjoor Newbie
    Currently Being Moderated
    i got the problem.

    it was from the on-error trigger.
    i handle the error, give a appropriate message for the error, but do not issue RAISE FORM_TRIGGER_FAILURE after the message.
    that's why form was considered to be successfull.
    for reference my on-error trigger is below, in hope it helps somebody, or somebody correct me and give me better idea.
    declare
    errcode number:=error_code;dbmserrcode number;dbmserrtext varchar2(200);
    formtxt varchar2(200);
    errtype varchar2(3);
    begin
    
    --------- user defined database exceptions errors raised by --RAISE_APPLICATION_ERROR
    /*IF DBMS_ERROR_CODE in (-20738,.......) then
    raise form_trigger_failure;
    end if;
    */
    /*
    if error_code=50017 then
    message('duration must be between 0 and 23);message(' ',no_acknowledge);
    raise form_trigger_fialure;
    elsif
    */
    
    if error_code=40202 then
    clear_message;
    message('Please provide all the required fields.');message(' ',no_acknowledge);
    raise form_trigger_failure;
    /**
    elsif error_code=50022 then
    clear_message;
    message('time must be in the correct format.');message(' ',no_acknowledge);
    raise form_trigger_failure;
    */
    end if;
    if error_code in (40509,40508) then
    dbmserrcode:=dbms_error_code; dbmserrtext:=dbms_error_text;
    if dbmserrcode=-1438 then
    clear_message;
    message('value too large for column.');message(' ',no_acknowledge);
    raise form_trigger_failure;
    elsif dbms_error_code=-1400 then
    clear_message;
    message('please provide the required fields.');message(' ',no_acknowledge);
    raise form_trigger_failure;
    elsif dbmserrcode=-2291 then
    clear_message;
    message('header is not found for this insertion.');message(' ',no_acknowledge);
    raise form_trigger_failure;
    elsif dbmserrcode=-1 then
    clear_message;
    message('record already exists');message(' ',no_acknowledge);
    raise form_trigger_failure;
    else
    clear_message;
    message(error_code||' : '||error_text);message(' ',no_acknowledge);
    raise form_trigger_failure;
    end if;
    else
    clear_message;
    message(error_code||' : '||error_text||'on-error section (40509,40508)');message(' ',no_acknowledge);
    raise form_trigger_failure;
    end if;
    exception
    when others then
    /*handle user defined database errors here
    if dbms_error_code=-20738 then
    clear_message;
    message('Error: absent or later record errror from the database trigger.);
    raise form_trigger_failure;
    elsif .....
    ......
    ....
    
    esle
    */
    if dbms_error_code=-1400 then
    raise form_trigger_failure;
    else
    clear_message;
    message(dbmserrcode||' : '||dbmserrtext ||'Exception section');message(' ',no_acknowledge);
    raise form_trigger_failure;
    end if;
    end;
    i my case, i was forgotten in the exception section the follwing lines
    f dbms_error_code=-1400 then
    raise form_trigger_failure;
    else
    clear_message;
    message(dbmserrcode||' : '||dbmserrtext ||'Exception section');message(' ',no_acknowledge);
    raise form_trigger_failure; --------------i forgot this line.
    end if;
  • 2. Re: key-commit conflict with on-error
    user346369 Expert
    Currently Being Moderated
    From the Forms Builder online help on Form_Success builtin:
    FORM_SUCCESS should not be used to test whether a COMMIT_FORM or POST Built-in has succeeded.
    Because COMMIT_FORM may cause many other triggers to fire, when you evaluate FORM_SUCCESS it
    may not reflect the status of COMMIT_FORM but of some other, more recently executed built-in. A more
    accurate technique is to check that the SYSTEM.FORM_STATUS variable is set to QUERY after the
    operation is done.
    For that reason, all forms I work on have this:
    <pre> COMMIT_FORM;
    IF :SYSTEM.FORM_STATUS &lt;&gt; 'QUERY'
    OR NOT FORM_SUCCESS THEN
    RAISE FORM_TRIGGER_FAILURE;</pre>

Legend

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