Re: Tough data validation problem

> 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 Received on Thu Mar 15 2001 - 21:51:47 CST