Re: Restart increment to each year = re-invent the

Bruno Baguette said:
> I have to design a table wich will store some action reports. Each
> report have an ID like this 1/2004, 2/2004, ... and each years, they
> restart to 1 (1/2004, 1/2005, 1/2006,...).
>
> So, I was thinking to split that in two fields : one with the increment
> and one with the year. But I don't know how can I manage the sequences
> since I have to restart to each year...
>
> Do you think I should re-invent the sequences mecanism with a second
> table and a stored procedure, only to manage theses 'home-made' sequences
> ?
>
> Or should I create some sequences like myseq_2004, myseq_2004,
> my_seq_2005,... and use a concatenation of the myseq_ string and the
> current year when calling nextval and curvall ?

Of the options available, I would use the sequence solution; one sequence
object for each year.

You can do something along the following lines in a stored procedure
(which could be used to create the report records):

...
-- pass in the year value to the procedure
-- or determine year from year part of current date
yearNumberTxt = '2004';
-- get the next ID for the year
select nextval(yearNumberTxt) into seqNum;
-- and generate your report number
reportID := seqNum || '/' yearNumberTxt;
-- insert into your ReportTable using new report ID
...

If you use an extra table and manage the incrementing field yourself (your
other suggestion), then you need to be aware of concurrency issues when
accessing and updating the counter (for the year). You need to lock the
row in a function which generates the next number for the year, and this
will block any other processing wanting a number at the same time. Of
course if you don't create reports frequently, or concurrently then this
isn't an issue.

The sequence solution will not block, but could leave you with gaps in
your numbering if a record fails to insert for some reason after you issue
the nextval function on the sequence object.

John Sidney-Woollett