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