SET TERM ^ ;

CREATE OR ALTER procedure SP_UPDATE_GENERATOR
as
declare variable GENERATOR_NAME varchar(200);
declare variable POS integer;
begin

  for
    select
        RDB$GENERATOR_NAME
    from RDB$GENERATORS
  into
    :GENERATOR_NAME
  do begin

    in autonomous transaction do
    begin

        execute statement 'select max(controle) from ' || replace(:GENERATOR_NAME,'GTB','TB') into :POS;
        execute statement 'ALTER SEQUENCE '||:GENERATOR_NAME||' RESTART WITH '||coalesce(:POS,0)||' INCREMENT BY 1;';
        EXCEPTION;

        WHEN ANY DO
        BEGIN

        END
    end

    in autonomous transaction do
    begin

         execute statement 'select max(codigo) from ' || replace(:GENERATOR_NAME,'GTB','TB') into :POS;
         execute statement 'ALTER SEQUENCE '||:GENERATOR_NAME||' RESTART WITH '||coalesce(:POS,0)||' INCREMENT BY 1;';
         EXCEPTION;

         WHEN ANY DO
         BEGIN

         END
    end

    in autonomous transaction do
    begin

         execute statement 'select max(id) from ' || replace(:GENERATOR_NAME,'GTB','TB') into :POS;
         execute statement 'ALTER SEQUENCE '||:GENERATOR_NAME||' RESTART WITH '||coalesce(:POS,0)||' INCREMENT BY 1;';
         EXCEPTION;

         WHEN ANY DO
         BEGIN

         END
    end

  end

end^

SET TERM ; ^


