Re: Tough data validation problem

[Quoted] On Thu, 15 Mar 2001 19:51:47 -0800, "Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote:

>> Hello, I've got a unique data validation requirement.
>>
>> I've got two tables MASTER and DETAIL.
>>
>> One of the columns in DETAIL is PRIMARY_FLAG.
>>
>> In my form, which is a typical master detail form, the user can enter
>> a MASTER with one or more DETAILs. They must enter a value for the
>> PRIMARY_FLAG. Allowed values are 'Y' and 'N'. One and only one
>> detail must have a 'Y' the rest must have 'N'.
>>
>> How/where can I enforce this? I can't write a DB trigger because
>> AFTER INSERT will fire for each one. I can't do it in PRE-INSERT on
>> the DETAIL block because a later one may have the 'Y' set. I can't do
>> it in PRE/POST insert on the MASTER block, because the MASTER might
>> have been inserted previously and the operator is only coming back to
>> create DETAILS.
>>
>> Any ideas??
>
>Your requirement isn't that unique. You can either use a before-insert
>trigger to do this or do what I have seen most often which is to never
>insert directly into a table. Pass the values to a stored procedure and
>let it do the validation and inserting.
>
>Daniel A. Morgan

Yes, but. Lets say I have three rows in the table already number 1,2,3 and number 1 is PRIMARY=Y. Now, I want to update 1 to set PRIMARY=N and 2 to set PRIMARY=Y, I can't do it. Received on Fri Mar 16 2001 - 18:10:13 CET