Inoffizielles deutsches Xbase-Forum

Code: Alles auswählen

CREATE FUNCTION gen_id(cname cichar(255))
  RETURNS integer
BEGIN
  declare @numrows integer;
  declare @nextid integer;
  declare @rv integer;
  --create generator table if not exists
  if not exists(select * from system.tables where name like 'tb_generator') then
    create table tb_generator(id autoinc, name cichar(255), lastid integer, rv rowversion);
  endif;
  --check if there's already a generator for that name
  MERGE tb_generator ON name LIKE cname
    WHEN NOT MATCHED THEN INSERT(name,lastid) VALUES(cname,0);
    --now update that record until you got it (concurrency!)
    @numrows=0;
    while @numrows=0 do
      @rv=(select rv from tb_generator where name like cname);
      @nextid=(select lastid+1 from tb_generator where name like cname);
      try
        update tb_generator set lastid=@nextid where name like cname and rv=@rv;
        --if there's no update, another user already updated that record
        @numrows=(select ::stmt.UpdateCount from system.iota);
      catch all
        @numrows=0;
      end try;
    end while;
  return @nextid;
END;

Code: Alles auswählen

SELECT gen_id('test') FROM SYSTEM.IOTA;

Code: Alles auswählen

SELECT gen_id('4000000') from system.iota

Code: Alles auswählen

UPDATE mandanten SET kundennummer=(SELECT gen_id('Schüler') from system.iota)

Code: Alles auswählen

CREATE TRIGGER upd_mandanten ON mandanten AFTER UPDATE
BEGIN
  UPDATE mandanten SET kundennummer=gen_id('Schüler') WHERE kundennummer IS NULL and ROWID=::stmt.TrigRowid;
END

Code: Alles auswählen

UPDATE mandanten SET kundennummer=kundennummer;