Re: HELP: Tables with unique columns in Forms 3.0

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/02/04
Message-ID: <34d8e1c7.29724819@www.sigov.si>#1/1

On Wed, 04 Feb 1998 12:18:54 +1000, Mark Gebbie <gebbie.mark.m_at_bhp.com.au> wrote:

>...[SNIP]....
>The problem we are having is this. Suppose the first 2 records, in the
>database, have the numeric values of 3 and 4. The user uses the Form to
>change these to 4 and 3 respectively and commits the changes. The check
>then recognises the first record has changed to 4, it checks the
>database for anything else with a value of 4 and discovers the second
>record, still with its value of 4 (hasn't been updated in the database
>yet). So, even though on the screen all the values are unique, it
>displays an error saying duplicate values exist. The only way around it
>is to delete one of the records, then change the other, then re-create
>the first one with the new value. Messy.

I used to use the following technique for acompishing this:

In pre-update trigger offset the user-entered values to the values that could not possible exists in the table (allthough the database must permit them). In your example, supose there could not been negative values in the column, so you would set the offset to be -1000, evaluating your values 4,3 to -996,-997 respectvely. That way, the insert will not fail.

Then in the post-update triger return the values to their original value by adding the offset of 1000, resulting your values back to 4,3.

>
>Mark

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Wed Feb 04 1998 - 00:00:00 CST