Adaptation of Auto Increment to a Compound Key

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 Fri Nov 22 2002 - 15:43:02 CST