5 Replies Latest reply: Apr 9, 2013 9:11 AM by CraigB RSS

    How to check the data updated before saving the record?

    Veena
      Hi All,

      I am using forms 10g and developed a custom form with 4 tab pages. One tab shows data from a table and the data is updatable.

      When the save button is clicked on the form, i need to compare the old record and the new record, if they does not match, i need to display an alert with a message and which will have 'OK' and 'Cancel' buttons. 'Ok' will save the data and Cancel will not save.

      I have tried this using key-commit trigger at form level. But i see that the updated value is not fetched and shows null. I could see the updated value at the particular block level only.

      Please suggest how to proceed.


      Thanks,
      Veena
        • 1. Re: How to check the data updated before saving the record?
          Amatu Allah Neveen Ebrahim
          Hi
          if ur requirement is duplication then why u don't think of using...
          DUPLICATE_RECORD ;
          Amatu Allah
          • 2. Re: How to check the data updated before saving the record?
            987802
            If you want to code for it, then 1 way to approach this is to use a check in the key_commit trigger, such as:
            begin
                if :system.form_status = 'CHANGED' then
                    if show_alert('alert_save') = alert_button1 then
                        commit;
                    else
                        clear_form(NO_VALIDATE);
                        execute_query;
                    end if;
                end if;
            end;
            • 3. Re: How to check the data updated before saving the record?
              CraigB
              When the save button is clicked on the form, i need to compare the old record and the new record,...
              You can use the GET_ITEM_PROPERTY() built-in to get the DATABASE_VALUE for each item in your block. For example:
              /* Sample code to check Block.Item value */
              /* against the value in the table.       */
              DECLARE
                al_id   ALERT;
                al_button  NUMBER;
                v_db_value   VARCHAR2(1000);
                v_message  VARCHAR2(200);
                
              BEGIN
                v_db_value := Get_Item_Property('YOUR_BLOCK.YOUR_ITEM', DATABASE_VALUE);
                IF ( :YOUR_BLOCK.YOUR_ITEM = v_db_value ) THEN 
                  /* This assumes you have an Alert named "YOUR_ALERT" */
                  al_id := Find_Alert('YOUR_ALERT');
                  Set_Alert_Button_Property(al_id, ALERT_BUTTON1, LABEL, 'OK');
                  Set_Alert_Button_Property(al_id, ALERT_BUTTON2, LABEL, 'Cancel');
                  v_message := 'Your Message here....the max amount displayed is 200 char)';
                  Set_Alert_Property(al_id, ALERT_MESSAGE_TEXT, v_message);
                  
                  /* now show the alert and capture which button the user pressed */
                  al_button := Show_Alert(al_id);
                  
                  IF ( al_button = ALERT_BUTTON1 ) THEN 
                    -- Save your data...
                  ELSE
                    -- Cancel the save...
                  END IF;
                END IF;
              END;
              I have tried this using key-commit trigger at form level. But i see that the updated value is not fetched and shows null.
              You could perform this check in the Key-Commit or Pre-Commit, but frankly, the best place would be in a Before-Insert/Update trigger on the table. Using the method I demonstrated above, you would have to loop through each database item in your block to check the DATABASE_VALUE. This is a slow process so it will cause a noticable slowness when saving.

              Hope this helps.
              Craig...
              • 4. Re: How to check the data updated before saving the record?
                user346369
                When the save button is clicked on the form, i need to compare the old record and the new record, if they does not match, i need to display an alert with a message and which will have 'OK' and 'Cancel' buttons. 'Ok' will save the data and Cancel will not save.
                Why? So the user clicks the Save button, and then you want him to then click OK to really save the data?

                Forms already checks internally when you issue a commit_form. If changes are made, it automatically updates the values on the database. If nothing was changed, it will display 'No data to save" (or something to that effect).

                You can check :System.Form_Status and :System.Block_Status (or Get_Block_Property('BLOCK_NAME',Status) ), to see if anything has changed, too. I am not sure what you would achieve by comparing values within each field.

                I have tried this using key-commit trigger at form level. But i see that the updated value is not fetched and shows null. I could see the updated value at the particular block level only.
                Then you have coded something wrong. :Block.Item should return the value the user entered.

                CraigB wrote:
                Using the method I demonstrated above, you would have to loop through each database item in your block to check the DATABASE_VALUE. This is a slow process so it will cause a noticeable slowness when saving.
                Actually, the Database_Value is a value stored in a running form's memory, stored during the query process, so accessing it is very quick. There is no io to the database involved. Forms uses Database_Value in the record-locking process to check whether the locally stored Database_Value matches the online value. If they match, the row is locked; if they don't, forms issues the message that the record has been changed by another user, and fails the lock.
                • 5. Re: How to check the data updated before saving the record?
                  CraigB
                  CraigB wrote:
                  Using the method I demonstrated above, you would have to loop through each database 
                  item in your block to check the DATABASE_VALUE. This is a slow process so it will cause a 
                  noticeable slowness when saving.
                  Actually, the Database_Value is a value stored in a running form's memory, stored during the query process, so accessing it is very quick. There is no io to the database involved. Forms uses Database_Value in the record-locking process to check whether the locally stored Database_Value matches the online value. If they match, the row is locked; if they don't, forms issues the message that the record has been changed by another user, and fails the lock. >
                  @ Steve, I never said checking the DATABASE_VALUE would cause I/O or a network round-trip. :) I merely stated that the act of checking each item would be time consuming. In my opinion, performing this kind of check is unnecessary and in hind-sight, I should have just stated that instead of offering a coding solution. Personally, if the OPs need is to ensure record uniqueness - I prefer to use a unique key on the table versus client side checks.

                  You are right about the DATABASE_VALUE being stored in the Form so this check does not require a trip to the database. Thanks for the clarification. :)

                  Craig...