This discussion is archived
3 Replies Latest reply: May 25, 2011 3:33 PM by 864508 RSS

clear_record, clear_block and delete_record and single records

81607 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    ok thanks will try this and let u know.
  • 3. Re: clear_record, clear_block and delete_record and single records
    864508 Newbie
    Currently Being Moderated
    CraigB
    Thanks !

Legend

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