simple SQL or PL/SQL question

[Apprentice101]

I have a form with a master-detail blocks.

I would like that as a new detail record is created, a sequential number is generated for that particular master record only

for instance.
master record
Jonh

details (brothers)
mary
Susan
Karla

so I want to have a column next to the detail names that will have the values 1,2,3 respectevly. And as a new name gets inserted, (Claudia) a number 4 will be generated.

This numeric value should only affect John, so if Douglas (another master record) gets interted, the value should be 1 again for row one on the details section.

so how do I do this?
thanks!!

 

[lewisp]

You could use a piece of SQL like this in a pre-insert trigger

SELECT NVL(MAX(tab.id_column),0) + 1
FROM   your_table tab
WHERE  master_name = 'John';

Be careful with this, as you may need to lock the table before selecting, but you can lock just the row or rows for the master name.

If you do need to lock the table, try to do so allowing for the minimum time between locking and committing or rolling back.

Another alternative for this if you do ot like the idea of locking is to create a sequence for each master name you create. Of course, you could end up with hundreds of sequences and they are not guaranteed to be sequential!