Re: preventing 2 rows with same "flag" set

From: Eric Givler <egivler_at_flash.net>
Date: Wed, 11 Oct 2000 19:30:50 GMT
Message-ID: <Kd3F5.1666$ln6.191577_at_news.flash.net>

> 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. Received on Wed Oct 11 2000 - 21:30:50 CEST