Re: Help sequence problem!

The problem with sequences is that the allocated numbers is not necessarily used.
Also it depends on which trigger 'level' you allocate the sequence number.

For example, you can have a form which when a user skips off the primary key field (post-text-item), the sequence is interrogated and a value is returned (value 3001 returned in the field). However, if the user does not save the changes in the form, that sequence number is wasted and a gap is left in your primary key.

If you haven't done, it is probably best to have a before insert trigger on the database table which fires just before the insert. That trigger will get the next value from the sequence and allocate it to your primary key. This way, the risk of gaps is minimised.

Also, how many values are you cacheing in your sequence? If you don't specify a value, the default is 20. You can try setting it to 1 when creating the sequence. I think the syntax is

create sequence uni_number increment by 1 cache 1;

I'm 70% sure on this next part but if your next value in your sequence was sitting at 2 and the database was to crash, having a cache of 20 will make your sequence number 20 when the database is restarted.

HTH Mark

Ruiping Gao wrote in message <373A2EC8.60CA7C6B_at_dpiwe.tas.gov.au>...
>Hi,
>I have a problem about creating sequence.My purpose is to create a
>sequence which start from number 1 and increase by 1 every time. When I
>insert record into a table this sequence will insert my unique number
>into primary key fields.
>For example, I have a table called table1 which have two fields,
>siteid,sitename. Whenever people insert sitename, my sequence can put
>continuence number like 1,2,3,4,5... etc. into siteid fields. My SQL
>syntax for create sequence as following:
>
>create sequence uni_number increment by 1 order;
>
>Then I will use insert into table1(siteid,sitename)
>values(uni_number.nextval,'survey1')
>
>But after several times insert records I've got siteid number like this:
>
>siteid sitename
>1 survey1
>2 survey2
>20 sryvey3
>21 survey4
>23 survey5
>30 survey6
>...
>The result is not what I wanted, I don't know what the problem is, if
>anyone out there can give me a help I will be very grateful.
>
>--
>Ruiping
>
>e_mail:ruiping_at_dpiwe.tas.gvo.au
>
>
>
Received on Thu May 13 1999 - 03:13:28 CDT