/*ALIAS>prop_db*/

/*ManyStart>1640*/
delete from RDB$RELATION_CONSTRAINTS where rdb$relation_name = 'BINS' and rdb$constraint_type = 'PRIMARY KEY';
ALTER TABLE BINS ADD BIN_ID INTEGER NOT NULL PRIMARY KEY; 
CREATE GENERATOR GEN_BINS_ID;
SET GENERATOR GEN_BINS_ID TO 0;
/*ManyStop>1640*/

/*SQLStart>1641*/
CREATE TRIGGER BINS_BI FOR BINS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.BIN_ID IS NULL) THEN
    begin      
      NEW.BIN_ID = GEN_ID(GEN_BINS_ID,1);
    end
  new.bin_no = 'GEN' || new.bin_id;  
END
/*SQLStop>1641*/

/*SQLStart>1642*/
CREATE OR ALTER procedure FILLBINNO 
as
declare variable OLD_NO varchar(10);
declare variable CNT integer;
begin
  cnt = 0;
  for select bin_no from bins
      into :old_no do
      begin
        update bins set bin_ID = :cnt WHERE bin_no = :old_no;
        cnt = GEN_ID(GEN_BINS_ID,1);
      end
end
/*SQLStop>1642*/


/*SQLStart>1643*/
execute procedure fillbinno
/*SQLStop>1643*/

/*SQLStart>1644*/
drop procedure fillbinno;
/*SQLStop>1644*/