Checking for duplicate items, Forms4.5

From: Andy Hardy <aph_at_ahardy.demon.co.uk>
Date: Thu, 6 May 1999 15:14:41 +0100
Message-ID: <hCRQqBARPaM3EwAm_at_ahardy.demon.co.uk>



[Quoted] I have a simple table:

key_id, unique_text, description

where the key_id is the primary key and unique_text is unique and not null. My block displays 10 rows.

I'm unsure how to handle the validation required so that the unique_text is always unique!

For example, if I let Forms build it's own block it will put a whenvalidate  on unique_text to prevent the use of nulls. However, it will not check for uniqueness and relies on Oracle to reject the record during the insert/update.

I would like to be able to do the validation in both update and insert modes - insert is simple to program as I can perform a query within the when-validate. Update, however is causing me problems - it's possible for the user to change the text and then change it back. The whenvalidate  fires and my simple query now sees that this value also exists in the database, because it is now 'looking' at itself.

I've contemplated using before and after variables to store the current and fetched values, but in some of my larger tables this may be timeconsuming.

I've thought of using the rowid, but I don't think that Oracle can insert/update a block which contains these?

Any thoughts?

Andy

-- 
Andy Hardy. PGP key available on request
===============================================================
Received on Thu May 06 1999 - 16:14:41 CEST

Original text of this message