Thanks, I checked out that article along with finding a previous
recent thread that someone had the same kind of problem I had.

However, from what I've seen, that article covers a way to have a
single, reusuable sequential ID pool that isn't the primary key.
This is part of what I want, but I also need a seperate pool for
each member (see my previous example in this thread). In effect, we
have two tables, member and foo, and the basic foo schema might look
like:

CREATE TABLE foo (
idnum INT NOT NULL PRIMARY KEY,
memberid INT NOT NULL REFERENCES member,
fooid INT NOT NULL,
UNIQUE(memberid, fooid) <<<------- this is the important part, a
composite key that end-users use to reference any given entry
);

Maybe I'm just being brain dead, but how could I get multiple
seperate pools for each member using that method?

After I've thought about it a bit, the best solution I've gotten was
to 1. as per that article, not use the 'member' primary key field as
my member ID field, 2. for foo, use the "NEW.fooid = MAX(fooid) + 1
WHERE memberid = NEW.memberid" generation scheme and somehow write
lock it so that no other transaction could be creating a record in
that table at that time.

Robby

--- In firebird-support@yahoogroups.com, Alexandre Benson Smith
<iblist@t...> wrote:

> rbd523 wrote:
>
> >Alright, last question for the day :)
> >
> >Say I have a table called 'member', and a table called 'foo'.

Each

> >member may have n foo records for him (1:n relationship).
> >
> >It is simple enough to make a generator for foo that makes IDs
> >sequentially, such as:
> >
> >member 1 makes foo record, foo id 1
> >member 1 makes foo record, foo id 2
> >member 2 makes foo record, foo id 3
> >member 5 makes foo record, foo id 4
> >
> >and so on... But, how could generators be used if I wanted
> >to "scope" the foo ID spaces to a member record, such as:
> >
> >member 1 makes foo record, foo id 1
> >member 2 makes foo record, foo id 1
> >member 1 makes foo record, foo id 2
> >member 3 makes foo record, foo id 1
> >member 3 makes foo record, foo id 2
> >member 3 makes foo record, foo id 3
> >member 4 makes foo record, foo id 1
> >
> >Now, I can do this with a BEFORE INSERT trigger statement for foo
> >such as:
> >
> >NEW.myid = SELECT MAX(myid) + 1 FROM foo WHERE memberid =
> >NEW.memberid;
> >
> >But, of course I would like to use generators as that statement
> >above isn't really safe.
> >
> >Any ideas?
> >
> >Thanks,
> >
> >Robby
> >
> >
> Hi Robby,
>
> Take a look on the historic of the list or do a google for

Auditable

> Series (or look at IBO site, this doc are somewhere on this site).

The

> concept can help you a lot.
>
> see you !
>
> --
>
> Alexandre Benson Smith
> Development
> THOR Software e Comercial Ltda.
> Santo Andre - Sao Paulo - Brazil
> www.thorsoftware.com.br