Firebird sequence-backed ID shorthand
- by pilcrow
What do others do to simplify the creation of simple, serial surrogate keys populated by a SEQUENCE (a.k.a. GENERATOR) in Firebird = 2.1? I finc the process comparatively arduous:
For example, in PostgreSQL, I simply type:
pg> CREATE TABLE tbl (
> id SERIAL NOT NULL PRIMARY KEY,
> ...
In MySQL, I simply type:
my> CREATE TABLE tbl (
> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
> ...
But in Firebird I type:
fb> CREATE TABLE tbl (
> id BIGINT NOT NULL PRIMARY KEY,
> ...
fb> CREATE SEQUENCE tbl_id_seq;
fb> SET TERM !!;
> CREATE TRIGGER tbl_id_trg FOR tbl
> ACTIVE BEFORE INSERT POSITION 0
> AS
> BEGIN
> IF ((new.id IS NULL) OR (new.id <= 0)) THEN
> BEGIN
> new.id = GEN_ID(tbl_id_seq, 1);
> END
> END !!
> SET TERM ; !!
... and I get pretty bored by the time I reach trigger definition. However, I routinely make SEQUENCE-backed ID fields for temporary, developement and throw-away tables. What do others do to simplify this? Work with an IDE? Run a pre-processing, in-house perl script over the DDL file? Etc.
