Re: Restart a sequence regularly

Kathy Lo wrote:
> On 11/21/07, Richard Huxton <dev(at)archonet(dot)com> wrote:

>> You probably shouldn't attach any meaning to the numbers from a sequence
>> - they're just guaranteed to be unique, nothing else.

What you say here contradicts the following.

> Actually, the sequence is formed by 4-digit of year and 6-digit of
> sequence.

So you *are* attaching significance to the number (by adding the current
year to the front of it).

> So, it is required to change and restart the range of
> sequence at the beginning of every year. For example, at the beginning
> of 2008, the sequence should be changed to the range of 2008000001 -
> 2008999999 and restart at 2008000001. In the time of changing the
> sequence, it does not allow any users to get the unique number from
> this sequence. However, our staff don't want to do it manually because
> it is difficult for them to make sure no one accessing the sequence
> and our service cannot stop at that time. Therefore, I need to let the
> users to wait in the period of changing the sequence.

Don't block users - have multiple sequences. If you define my_seq_2007,
my_seq_2008, my_seq_2009 etc and then wrap access to them in a function
you can EXTRACT() the year from the CURRENT_DATE and use that to form
your per-year unique value.

--
Richard Huxton
Archonet Ltd