Re: Sequence in SQL*FORM (help required)

In article <39nilu$n05_at_crocus.csv.warwick.ac.uk>, Mr W He (esrbs_at_csv.warwick.ac.uk) writes:
>Hello everybody
>
>I have a question regarding the SQL Form. I would like to hear a suggestion
>from you. Any response is greatly apprepriated.
>
>I have created a form. One of the fields, called REF_NO, is defined as
>unique, not null number. It takes a number from a definied sequence which
>start with 2000. My objective is to have a number in the field every time
>I want to add a new entry. It should always start from the smallest available
>value. I defined the FIELD DEFAULT for REF_NO as :sequence.sirseq.nextval,
>where 'sirseq' is the sequence name. When I excuted the form, it started from
>2000 as I expected. The problem I have is that every time I excute the form,
>the number increased by 1, even there is no new entry. My question is how to
>generate the REF_NO which always take the number one after the last entry.
>
>
>My another question is this. I have some entries in the form inserted by
>'insert into' in SQL, which took the REF_NO from 2000 to 2010. The
>REF_NO in the form takes the number from the same sequence described above.
>When I excuted the form I expected the number REF_NO start from 2011. But it
>always started from 2000. I would like to know how to solve this problem.
>
>Thank you for your attention.
>
>Wei
>

On your first point the easiest way to ensure that a sequence is added only when a record is to be inserted is to assign it on a PRE-INSERT trigger. Something along the lines of

select sequence_name.nextval
into :field_name
from dual

Unfortunately this will only display the new sequence number after an action causing a commit or a post has occurred.

As for your second point, the sequence should have been incremented after each insert into, provided they contained the sequence_name.nextval. This might be a silly question but were the SQL statements and the form executed under the same user, or even seperate users with synonyms to the same sequence ?

Hope this helps.

Regards

Allan Received on Tue Nov 15 1994 - 21:24:05 CET