2 Replies Latest reply: Feb 15, 2013 11:12 AM by user346369 RSS

    key-commit conflict with on-error

    Maahjoor
      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
          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
            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>