Fórum AUTO INCREMENT FIREBIRD #463478

CREATE GENERATOR AGenerator;

SET GENERATOR AGenerator TO 1;

  SELECT GEN_ID(AGenerator, 1) from RDB$DATABASE;

CREATE GENERATOR CUST_NO_GEN;

SET GENERATOR CUST_NO_GEN TO 0;

SET SQL DIALECT 3;

CREATE GENERATOR CUST_NO_GEN;

SET TERM ^ ;



CREATE OR ALTER TRIGGER SET_CUST_NO FOR CUSTOMER
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    if (new.cust_no is null) then
    new.cust_no = gen_id(cust_no_gen, 1);
END
^


SET TERM ; ^

function GeneratorID (aName: string; Connection: TSQLConnection;
  Incrementa: Boolean): integer;
var
  Qry: TSQLQuery;
begin
  Qry := TSQLQuery.Create(nil);
  try
    Qry.SQLConnection := Connection;
    if Incrementa then
      Qry.SQL.Add(
        'SELECT GEN_ID('+aName+', 1) FROM RDB$DATABASE')
    else
      Qry.SQL.Add(
        'SELECT GEN_ID('+aName+', 0) FROM RDB$DATABASE');
    Qry.Open;
    Result := Qry.Fields[0].AsInteger;
  finally
    FreeAndNil(Qry);
  end;
end;

function IncrementaPK(NomeGenerator_Tabela: string; i: Integer;
  CampoIncremento: string): Integer;
var
  x, Generator: string;
  Q1, Q2, Q3: TSQLQuery;
const
  sSqlFindGenerator = 'SELECT RDB$GENERATOR_NAME AS GEN FROM RDB$GENERATORS WHERE UPPER(RDB$GENERATOR_NAME) = ''%s''';
  sSqlGetGenerator = 'SELECT GEN_ID(%s,%s) AS INC FROM RDB$DATABASE';
  sSqlMaxField = 'SELECT COALESCE(MAX(%s),0) AS MAXFIELD FROM %s';
  sSqlSetGenerator = 'SET GENERATOR %s TO %s';
begin
  Generator := UpperCase('G_' + NomeGenerator_Tabela);
  Generator := Copy(Generator, 1, 31);
  Q1 := ExecSql(Format(sSqlFindGenerator, [Generator]));
  try
    if Q1.FieldByName('GEN').IsNull then
      begin
        Connection.ExecuteDirect(' CREATE GENERATOR ' + Generator);
        x := Format(sSqlMaxField, [CampoIncremento, NomeGenerator_Tabela]);
        if Trim(CampoIncremento) <> '' then
          begin
            if Existe_Tabela(UpperCase(NomeGenerator_Tabela)) then
              begin
                Q2 := ExecSql(x);
                try
                  Connection.ExecuteDirect(Format(sSqlSetGenerator, [Generator, Q2.FieldByName('MAXFIELD').AsString]));
                finally
                  FreeAndNil(Q2);
                end;
              end;
          end;
      end;
  finally
    FreeAndNil(Q1);
  end;
  Q3 := ExecSql(Format(sSqlGetGenerator, [Generator, IntToStr(i)]));
  try
    Result := Q3.FieldByName('INC').AsInteger;
  finally
    FreeAndNil(Q3);
  end;
end;

function ExecSql(X: String): TSQLQuery;
begin
  Result := TSQLQuery.Create(nil);
  Result.SQLConnection := Connection;
  Result.Close;
  Result.SQL.Clear;
  Result.SQL.Add(X);
  Result.Open;
end;

function Tdm.GeneratorID(aName: string; Connection: TIBConnection;
  Incrementa: Boolean): integer;
var
  Qry: TSQLQuery;
begin
  Qry := TSQLQuery.Create(nil);
  try
    Qry.DataBase := Connection;
    if Incrementa then
      Qry.SQL.Add(
        'SELECT GEN_ID('+aName+', 1) FROM RDB$DATABASE')
    else
      Qry.SQL.Add(
        'SELECT GEN_ID('+aName+', 0) FROM RDB$DATABASE');
    Qry.Open;
    Result := Qry.Fields[0].AsInteger;
  finally
    FreeAndNil(Qry);
  end;

end;                                   

procedure Tdm.SQLFUNCIONARIONewRecord(DataSet: TDataSet);
begin
     SQLFUNCIONARIO.FieldByName('COD_FUNCIONARIO').Value:=GeneratorID('GEN_FUNCIONARIO_ID',dbtransportes,TRUE);
end;