Subject Re: [firebird-support] Re: How to populate a new table column with a INTEGER sequence?
Author Martijn Tonies (Upscene Productions)
Post date 2014-09-22T12:39:22Z
Wouldn’t leaving out the “temp trigger” and “foo” and simply doing:
 
update sometable set id = gen_id(some_table_id_gen, 1);
 
have the same result?
 
With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!
 
 
 
Sent: Monday, September 22, 2014 2:36 PM
Subject: [firebird-support] Re: How to populate a new table column with a INTEGER sequence?
 


Hi to all,
 
This is one approach (Maybe could be done one more efficient):
 
/**
* Firebird 2.5
*/
 
-- 1.Create new fields
ALTER TABLE some_table
  ADD id INTEGER DEFAULT 0 NOT NULL,
  ADD foo_dummy CHAR(1);
 
-- 2.Create generador
CREATE GENERATOR some_table_id_gen;
 
-- 3.Create temp trigger
SET TERM ^ ;
CREATE TRIGGER some_table_temp_trgr FOR some_table
ACTIVE BEFORE UPDATE POSITION 0
AS
BEGIN
  new.id = GEN_ID(some_table_id_gen, 1);
END^
SET TERM ; ^
 
-- 4.Update foo_dummy field and so set the sequency
--   into field ID
UPDATE some_table SET foo_dummy = '1';
 
-- 5.Drop temp trigger and foo_ new PK
ALTER TABLE some_table ADD CONSTRAINT some_table_pk PRIMARY KEY (ID);
 
-- 9.Create new trigger for mantaining new PK
SET TERM !! ;
CREATE TRIGGER some_table_set_id FOR some_table
BEFORE INSERT POSITION 0 AS
BEGIN
  NEW.id = GEN_ID(some_table_id_gen, 1);
END !!
SET TERM ; !!
 
/* */
 
Best regards,
 
Hernando.