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

    clear_record, clear_block and delete_record and single records

      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
      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
          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 */
             tID     TIMER;
             /* 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;
          /* When-Timer-Expired trigger */
             tID     TIMER;
             tID := Find_Timer('DUP_RECORD');
          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
            ok thanks will try this and let u know.