Re: How to prevent duplicate values in column and allow only a certain range ?

From: Alex Heney <HENEYA_at_ENTCF3.agw.bt.co.uk>
Date: 1997/05/29
Message-ID: <338d2f43.3140766_at_news.axion.bt.co.uk>#1/1


Jens U. Veigel <jens_at_deutschware.com> wrote in article <338b083c.801392_at_news.aud.alcatel.com>...
> Hi Pros !
>
> Got a forms4.5 App, that allows entry of numbers into a table.
> One column of this table has let's say row number values that are
> allowed 1-10.
>
> How do I force my users, or notify them that when they try to
> enter a value (a number) in this particular column, that a row with
> this number in that particular column already exist, and therefore
> are not allowed to use this number). And how do I define a possible
> range of numbers that is allowed for entry ?
>
> So if the column has already rows numbers with 1,2,3,4,5,6,7,9,10,,,
> the only value allowed should be 8, if anything else is input I need
> to message the user. Have no trouble on the message part, but how do I
> check for duplicate entires, at comit time.
>
> I like the "format mask" "MM-DD-YY" type validation, where I tell the
> user what the format is , so he can retype it . Is this possible for
> checking on duplicates, and if they are excist to flag the user ?
>

Just do a select (using a cursor) in a WHEN-VALIDATE-ITEM trigger. If a matching record is found, then put out the error message.

You also probably need to do a similar check in a PRE-UPDATE or PRE-INSERT trigger on the record, unless you are forcing the user to commit after each record. This is because the above check will pick up where a duplicate is already on the database, but will not pick up the case where you have entered or modified two records to have the same value before they are committed.

--
The above posting represents the personal opinions of the author and
is not to be taken as official (or unofficial) policy or opinions of 
his employer.

Alex Heney, Living in the Global Village.
Received on Thu May 29 1997 - 00:00:00 CEST

Original text of this message