Subject 'scoping' generator sequences?
Author rbd523
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