Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

validate duplicate data entry by user on form

532302Sep 6 2006 — edited Sep 7 2006
On a form I have a block, in the block I have 3 fields. On block level on 'when-validate-record' I am trying to write a trigger. what I am trying to do is the following:-
If the user enters 'abcd' in the field... it's o.k
then he moves on to enter another record without saving the first record and enters 'abcd' again in the same field. (means the data is not saved in the table yet)

I want to pop up a message here and tell the user that he has entered the same data before in one of the records and he cannot do it again.
The trigger should be able to scan for all the records in the block and see if the data he is entering has been entered before on the form. I also have to check wether the data does not exist in the table as well.

that means I have to validate that 'abcd' should not be in the table and also 'abcd' should not be on the form as entered by the user.

Does anyone know what to do? this is what I am doing but it does not work?
v_orig_position := :system.trigger_record;
v_tax_id := :SITE_BLK.TAX_ID;

first_record;
loop
if v_tax_id = :SITE_BLK.TAX_ID and :SITE_BLK.TAX_AUTHORITY
= 'WSBC' THEN
v_taxid_count := v_taxid_count + 1;
end if;

if (:system.last_record = 'TRUE') then
go_record(v_orig_position);
EXIT;
else
next_record;
end if;
end loop;

IF v_taxid_count > 1 THEN
FND_MESSAGE.SET_STRING(NVL(:site_blk.tax_id,'notaxid') ||' id already entered! ');
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
eND IF;

it gives me error message 'illegal restiction first_record' and the logic does not work..
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 5 2006
Added on Sep 6 2006
7 comments
1,807 views