Re: Adaptation of Auto Increment to a Compound Key

You could have a sequence for each file name. In the trigger you would call for the next sequence by passing in the file name and if the sequence of that name was not found then create it.
Jim
"John O'Hara" <jjo135_at_hotmail.com> wrote in message news:afc9a69b.0211200713.49190170_at_posting.google.com...
> I was curious if anyone knows if it is possible to implement the
> following in Oracle:
>
> MySQL has a feature called Auto-Increment, which is similar to Oracle
> sequences, however it was one some additional features that (I don't
> believe) sequences can provide.
>
> In MySQL, I can define the following table
>
> create table REVISION
> (
> filename VARCHAR2(256) not null,
> revision INTEGER not null AUTO_INCREMENT,
> constraint REVISION_PK primary key (filename, revision)
> );
>
>
> After doing so, we can run the following inserts:
>
> insert into revision(filename) values ('foo.txt');
> insert into revision(filename) values ('bar.txt');
> insert into revision(filename) values ('foo.txt');
> insert into revision(filename) values ('foo.txt');
> insert into revision(filename) values ('bar.txt');
>
> we get the resulting table
>
> filename revision
> foo.txt 1
> bar.txt 1
> foo.txt 2
> foo.txt 3
> bar.txt 2
>
> That is, a separate sequence is maintained for each filename. I have
> not found a way to do this in Oracle, short of querying the table each
> time for max(revision) where filename=? and incrementing it, but this
> seems error prone. Is there native support for this, or would I be
> forced to write some sort of trigger?
>
> Regards,
> John
Received on Wed Nov 20 2002 - 16:54:24 CET