Subject | 'scoping' generator sequences? |
---|---|
Author | rbd523 |
Post date | 2004-07-14T14:25:39Z |
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
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