Ann W. Harrison wrote:

> At 03:55 PM 10/27/2004, =?ISO-8859-2?Q?Bogus=B3aw_Brandys?= wrote:
>
>
>>Hello,
>>
>>I'd like to know how in Firebird is possible to have unbroken series of
>>for example document's numbers but wrapped monthly for example.
>>1\01\2004
>>2\01\2004
>>3\01\2004
>>...
>>1\02\2004
>>2\02\2004
>
>
> The answer depends somewhat on how many documents you expect to store
> and whether you need to be absolutely certain that you never get a hole
> in the sequence.
>
> If the answer that holes are OK, then use a generator and reset
> it monthly.
>
> If the answer is a few, and holes are not OK, then setup a table
> that contains only the next number. Reset the number monthly.
> Increment the value by updating the record that holds it before
> storing the document. If your transaction rolls back, the number
> becomes available again.

Well, I found one problem in this implementation. Curently I used it as
You descibed - I update the record storing the document using SP listed
below in "on before insert" triggers, but I suspect that this cause
bottleneck becouse after that I store doc header and many others records
in a few tables, so probably this record is hold during long period of
time.Maybe attemp to update this record and genrate next doc number
should be AFTER storing document with header and all other informations
? Could this lead to hold this record for shorter period of time ?
What type of transaction isolation should I use - snapshot or readcommited ?


Regards
Bogusław Brandys


CREATE TABLE TYPYDOK (
TYP TYPDOK /* TYPDOK = VARCHAR(5) NOT NULL */,
NAZWA VARCHAR(50) NOT NULL,
OPIS VARCHAR(255),
NUMERACJA ZAKRES NOT NULL /* ZAKRES = CHAR(1) DEFAULT 'R' NOT NULL
CHECK (VALUE IN ('R','M','B')) */,
WZORZEC BINARY /* BINARY = BLOB SUB_TYPE 0 SEGMENT SIZE 80
DEFAULT NULL */
);




CREATE TABLE TYPYDOK_NR (
ID BIGINT NOT NULL,
IDMAG INTEGER,
TYP VARCHAR(5) NOT NULL,
OSTATNIO TIMESTAMP DEFAULT 'NOW' NOT NULL,
NRKOL INTEGER DEFAULT 1 NOT NULL
);



CREATE PROCEDURE DODAJ_NR (
TYP VARCHAR(5),
IDMAG INTEGER)
RETURNS (
NUM BIGINT)
AS
DECLARE VARIABLE TYPNR CHAR(1);
DECLARE VARIABLE CURYEAR INTEGER;
DECLARE VARIABLE CURMONTH INTEGER;
DECLARE VARIABLE OLDMONTH INTEGER;
DECLARE VARIABLE OLDYEAR INTEGER;
BEGIN
SELECT NUMERACJA FROM TYPYDOK WHERE TYP=:TYP INTO :TYPNR;
IF (TYPNR IS NULL) THEN EXCEPTION BRAK_TYPU_DOK;
CURYEAR=EXTRACT(YEAR FROM CURRENT_DATE);
CURMONTH=EXTRACT(MONTH FROM CURRENT_DATE);
SELECT EXTRACT(YEAR FROM OSTATNIO),EXTRACT(MONTH FROM OSTATNIO) FROM
TYPYDOK_NR WHERE (IDMAG=:IDMAG) AND (TYP=:TYP) INTO :OLDYEAR,:OLDMONTH;
IF (OLDYEAR IS NULL) THEN BEGIN
INSERT INTO TYPYDOK_NR(TYP,IDMAG) VALUES(:TYP,:IDMAG);
NUM = 1;
EXIT;
END
ELSE
BEGIN
IF (((TYPNR='R') AND (CURYEAR<>OLDYEAR)) OR ((TYPNR='M') AND
(CURMONTH<>OLDMONTH))) THEN
UPDATE TYPYDOK_NR SET NRKOL=1,OSTATNIO=CURRENT_TIMESTAMP WHERE
(IDMAG=:IDMAG) AND (TYP=:TYP);
ELSE
UPDATE TYPYDOK_NR SET NRKOL=NRKOL+1,OSTATNIO=CURRENT_TIMESTAMP WHERE
(IDMAG=:IDMAG) AND (TYP=:TYP);
SELECT NRKOL FROM TYPYDOK_NR WHERE (IDMAG=:IDMAG) AND (TYP=:TYP) INTO
:NUM;
END
END