3 Replies Latest reply: May 25, 2011 5:33 PM by 864508 RSS

    clear_record, clear_block and delete_record and single records

    81607
      I have a block that displays up to 15 records. My problem is if a user enters a record that exists already in the database, I am using the when-validate-record trigger to check for existence in the table:

      select count(1) into v_count
           from p_comps_opp_admin
           where emp_id=:p_comps_opp_admin.emp_id;

      if v_count!=0 then
      clear_record;
      end if;

      The above does not work where the duplicate record that the user entered is the only one in the block. Similarly delete_record and clear_block do not work either.
      The record is not cleared, it stays put and the system/form gets stuck.

      I have also tried putting this same code in a when button pressed trigger and the result is the same.

      What can I do to achieve the goal of clearing a duplicate from the form? Please help.

      Thanks a lot
        • 1. Re: clear_record, clear_block and delete_record and single records
          CraigB
          Part of the problem is that the When-Validate-Item (WVI) trigger does not allow execution of Restricted Built-ins. The Clear_Record and Delete_Record built-ins are Restricted; which means you can't call them from the WVI trigger. You can get around this limitation by capturing the record number of the row to clear and then creating a timer in your WVI trigger and then call the Clear_Record or Delete_Record built-ins from the When-Timer-Expired (WTE) trigger. For Example:
          /* When-Validate-Item trigger */
          DECLARE
             tID     TIMER;
          BEGIN
             /* Your Validation Logic goes here */
             .......
             IF <Duplicate Record Found> THEN 
                /* Capture the record to be cleared */
                :Global.Dup_Record := :System.Trigger_Record;
          
                /* Create a timer with a 100 Millisecond delay */
                tID := Create_Timer('DUP_RECORD', 100, NO_REPEAT);
             END IF;
          END;
          /* When-Timer-Expired trigger */
          DECLARE
             tID     TIMER;
          BEGIN
             tID := Find_Timer('DUP_RECORD');
             Go_Block('MY_BLOCK');
             Go_Record(:GLOBAL.Dup_Record);
             Clear_Record;
          END;
          You might also want to take a look at the Avoid duplicate Records in a Block article as this method gives you a method to check for duplicates that are only in your Form and have not been commited to the database yet.

          Hope this helps,
          Craig B-)

          If a response is helpful or correct, please mark it accordingly.
          • 2. Re: clear_record, clear_block and delete_record and single records
            81607
            ok thanks will try this and let u know.