Vorgehensweise für Rechnungsnummernvergabe im Multi-User Betrieb

[Sir Rufo]

Mit MySQL würde das so aussehen:

Code:

CREATE TABLE `beltab` (
`BelID`  bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
`BelTyp`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`BelNr`  int(11) NOT NULL DEFAULT 0 ,
`BelInfo`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`BelID`),
UNIQUE INDEX `UNQ_Typ_Nr` (`BelTyp`, `BelNr`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1044
ROW_FORMAT=COMPACT;

CREATE DEFINER=`root`@`%` TRIGGER `beltab_before_insert` BEFORE INSERT ON `NewTable`
FOR EACH ROW SET NEW.BelNr = GetNewScopeLfd( NEW.BelTyp );

CREATE TABLE `tbl_lfd` (
`Scope`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`LastLfd`  int(11) NOT NULL ,
`Step`  int(11) NOT NULL DEFAULT 1 ,
PRIMARY KEY (`Scope`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT;

CREATE DEFINER = `root`@`%` FUNCTION `GetNewScopeLfd`(Scope varchar(50))
 RETURNS int(11)
BEGIN
   DECLARE NewLfd int;

   UPDATE tbl_lfd
      SET LastLfd = LastLfd + Step
      WHERE tbl_lfd.Scope = Scope;

   SELECT LastLfd
      INTO NewLfd
      FROM tbl_lfd
      WHERE tbl_lfd.Scope = Scope;

   RETURN NewLfd;
END;

Zum Testen habe ich folgendes (mit 10+ Programm-Instanzen) ausführen lassen:

Delphi-Quellcode:

unit View.Main;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics,
  Controls, Forms, Dialogs, DB, DBAccess, Uni, UniProvider,
  MySQLUniProvider, StdCtrls, ExtCtrls;

type
  TForm1 = class( TForm )
    MySQLUniProvider1 : TMySQLUniProvider;
    UniConnection1 : TUniConnection;
    Timer1 : TTimer;
    CheckBox1 : TCheckBox;
    procedure CheckBox1Click( Sender : TObject );
    procedure Timer1Timer( Sender : TObject );
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form1 : TForm1;

implementation

{$R *.dfm}

procedure TForm1.CheckBox1Click( Sender : TObject );
begin
  Timer1.Enabled := CheckBox1.Checked;
end;

procedure TForm1.Timer1Timer( Sender : TObject );
begin
  Timer1.Enabled := False;

  if not UniConnection1.Connected
  then
    UniConnection1.Open;

  UniConnection1.StartTransaction;
  try

    UniConnection1.ExecSQL( 'INSERT INTO beltab (BelTyp, BelInfo) VALUES(:BelTyp,:BelInfo);',
      ['RECHNUNG', 'Nur so'] );

    Sleep( 250 );

    if Random( 2 ) = 1
    then
      UniConnection1.Commit
    else
      UniConnection1.Rollback;

  except
    UniConnection1.Rollback;
  end;

  Timer1.Enabled := CheckBox1.Checked;
end;

end.

Es treten weder Lücken auf, noch kommt es zu Ausnahmen, weil eine Beleg-Nummer doppelt vergeben wurde.