Re: DEV2000 - Oracle Form 4.5 - Detail records, add 1 to seq_no field

In article <1997Sep15.164829.9490_at_vmsmail.gov.bc.ca>, gdimopou_at_kamloops.env.gov.bc.ca says...
>
>I have a master-detail app,
>
>master table has loc_key as unique primary key
>detail table has loc_key+seq_no as unique primary key
>
>My app allows user to enter 1 master record and many detail records.
>At the detail record, I would like to manage the generation of
>unique values for seq_no for a given master record identified by loc_key.
>
>Without saving each detail record, and then find the next seq_no
>value, how would I increase the value of seq_no?
>
>Seq_no for current record would be max seq_no value for all records
>on the current form + 1
>
>note: not all detail records have been saved to the database
>
>Any ideas would be greatly appreciated
>
>thanks in advance
>
>George D
>
>if you don't mind, could the answers be sent to my
>email account directly gdimopou_at_kamloops.env.gov.bc.ca
>
>I will post answers after I recieve them
>

I think you've got the answer by now, but anyway.

Is there any special mechanisms built to avoid multiple users inserting detail records to a master at the same time?

If yes, then cache the last seq_no in client software and use it when a record is created on screen. Watch for situations where user abandons the newly created record, especially if you want to prevent holes in the sequence.

If no, consider creating special sequence logic to your server to generate unique numbers on transactional basis. Lock something to prevent other sessions from obtaining numbers when one is just about inserting new records. May use DBMS_LOCK or just lock the entire detail table. Think of the lock like a semaphore.

Ask for clarification/examples, if needed!

Regards,
Jan Received on Wed Sep 17 1997 - 00:00:00 CEST