Subject RE: [firebird-support] Re: Sequential auto incremental numbering
Author Christian Giesen
Post date 2018-09-03T06:29:03Z

Good day Steve,

 

Thanks for your response. As I mentioned the issue is not to generate unique ID values. This is taken care of by using generators (as you correctly mention) or, now with Firebird 3, I have implemented the IDENTITY method of generating unique ID values. The issue that I have is that the generation of ID values via generators or the IDENTIY method does NOT guaranty sequential numbering. This is identical to MS SQL server’s IDENTITY field type as well as their sequence generators.

 

An invoice system HAS TO guarantee sequential numbering! I dug a bit deeper into this over the weekend and have successfully implemented this as follows:

 

1.       The INVOICE table contains and ID field (INTEGER, PRIMARY KEY, AUTO INCREMENT AS IDENTITY) as well as an INVOICE_NO field. This field stores the sequential invoice number.

2.       I have implemented an AFTER INSERT trigger on the INVOICE table as follows:

DECLARE VARIABLE NextInvNo INTEGER;

BEGIN

  NextInvNo = (SELECT MAX(INVOICE_NO) FROM INVOICE) + 1;

 

  IF (NextInvNo IS NULL) THEN

    NextInvNo = 1;

 

  UPDATE INVOICE

    SET INVOICE_NO = :NextInvNo

  WHERE

    ID = NEW.ID;

END

 

Thanks again for your input.

 

Kind regards,

 

 

Christian Giesen

Managing Member

Express Talent cc

 

Mobile: +27 82 574 5566

E-Mail:   chris@...

Alt Email: cvgiesen@... or cvgiesen@...

 

This message contains private and confidential Information. If you are not the intended addressee indicated in this message or you are not responsible for the delivery of such messages to the intended recipient, you may not copy or deliver this message to anyone. You may not use any information gleaned from this message for the benefit or promotion of yourself or your organization. If such is the case, please destroy this message immediately and kindly notify the sender by return of E-Mail. Express Talent cc takes no responsibility whatsoever resulting in you misusing information contained in this message and doing so may render you liable for the consequences of misusing said content of this E-Mail message.

 

From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com>
Sent: Monday, 03 September 2018 00:19
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Sequential auto incremental numbering

 

 

Hi Christian...

 

I use Firebird Generators for primary-key generation and they work fine...

 

Here is the generated Firebird-PSQL for a table in my current project development, which uses a generator to create a primary-key number upon insertion...

 

>>>  

CREATE TABLE RI_DOCUMENT_STORAGE (
  DS_KEY BIGINT NOT NULL,
  DS_DOCUMENT_MASTER_KEY BIGINT NOT NULL,
  DS_DOCUMENT BLOB NOT NULL);


SET TERM ^ ;

CREATE TRIGGER BI_RI_DOCUMENT_STORAGE_DS_KEY FOR RI_DOCUMENT_STORAGE
ACTIVE BEFORE
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.DS_KEY IS NULL) THEN
      NEW.DS_KEY = GEN_ID(RI_DOCUMENT_STORAGE_DS_KEY_GEN, 1);
END^

SET TERM ; ^

<<<  

 

A good database manager for Firebird will do this for you... 

 

Steve Naidamast

Sr. Software Engineer