Re: preventing 2 rows with same "flag" set

Eric Givler wrote:
>
> > Can't you set the item to be 'unique' (and have a unique key underneath
> > it) with the only valid values being Y and null
>
> The table layout:
>
> user_eid number
> location_eid number
> primary_location_Flag varchar2(1)
> security_level number
>
> PK: user_eid, location_eid
>
> Are you saying, create a Unique index for User_EID and the primary_location
> "flag"? Even if this is the case, how do I trap the user from toggling the
> flag on for each row? The block has primary key enabled and the primary
> fields are already entered.
>
> To "try" and enforce the requirement, I put in this code:
>
> block:when-validate-record
> begin
> Validate_User_Record;
> end;
>
> block:when-new-record-instance
> Post_Transactions;
>
> primary_park:when-checkbox-changed
> Dont_allow_multiple_checks;
>
> Validate_User_Record also calls Dont_allow_multiple_checks
>
> Here's some more source:
>
> PROCEDURE dont_allow_multiple_checks IS
> v_flag_found BOOLEAN := FALSE;
> v_dummy CHAR(1) := NULL;
>
> cursor flag_checker is
> select 'X'
> from application_user_profiles
> where primary_location_flag = 'Y'
> and user_eid = :profiles.user_eid
> and location_eid <> :profiles.location_eid;
> BEGIN
>
> if :profiles.primary_location_flag = 'Y' then
>
> open flag_checker;
> fetch flag_checker into v_dummy;
> v_flag_found := flag_checker%FOUND;
> close flag_checker;
>
> if v_flag_found THEN
> AlertSend('You can only have ONE primary location.');
> end if;
>
> end if;
>
> END;
>
> PROCEDURE Post_Transactions IS
> old_msg_level varchar2(2) := :system.message_level;
> BEGIN
> if :system.BLOCK_status in ('INSERT','CHANGED') THEN
> :system.message_level := '25';
> POST;
> :system.message_level := old_msg_level;
> END IF;
> END;
>
> PROCEDURE validate_profile_record IS
> -- changed from MessageSend to AlertSend
> BEGIN
> if :profiles.ndb_username is null then
> AlertSend( 'You must enter a Username', TRUE );
> end if;
>
> if :profiles.ndb_location_name is null then
> AlertSend( 'You must enter a Location Name', TRUE );
> end if;
>
> if :profiles.security_level is null then
> AlertSend( 'You must enter a security level', TRUE );
> end if;
>
> dont_allow_multiple_checks;
>
> END validate_profile_record;
>
> The intent of the when-new-record-instance was to make sure the record was
> posted before you moved to a new one and that way you could look for the
> "flag". Unfortunately, it's a multi-record block and I can click on one
> checkbox on record1, then the next on record2, and the when-checkbox-changed
> fires PRIOR to the when-new-item-instance. The new record doesn't see this
> as a problem (the old data has yet to be posted) and then I can hit commit
> and EUREKA, two records with primary flags set.
>
> This seems so trivial. What am I missing?
>
> Thanks for the info.

[Quoted] If you had a unique key on user_eid and flag (restricted to null and Y), then at commit time, if someone's done something wrong, they'll get a unique constraint violation...

Getting forms to pick that up earlier may be the challenging bit...

Cheers

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk
(faster/mirrored at http://www.oradba.freeserve.co.uk)

Its not the voices in my head that bother me... 
  its the voices in yours.