sub-position counter

Hello,

having a table
t (id integer, sub_id integer)
how is it possible to autoincrement the sub_id per id? The table should simply be filled like that:

id         sub_id
1            1
1            2
1            3
2            1
2            2
2            3
2            4
3            1
4            1
4            2

How is it possible to automatically increment the sub_id without mentioning it
in the INSERT statement?
Normally in a trigger I would refer to the maximum

select nvl(max(sub_id),0) + 1 from t where id=:new:id but i get the mutating table error!
Considering your solution to use "before insert"-, "before insert for each
row"-, "after insert"- triggers and with appropriate global state variables (in
a globals_pkg package), I think I would have to UPDATE the row in question in
the "after insert" trigger:

update table t set sub_id=(select nvl(max(sub_id),0) + 1 from t where id =
globals_pkg.id(i))
where id = globals_pkg.id(i)

but that doesn't work either (no error-message, but the update just doesn't
happen), presumable because you can't change the :new values anymore at that
point?!
Is there any reasonable way to accomplish my task?

Thanks, Michael Received on Wed Feb 15 2006 - 00:50:12 CST