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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
