The answer is well known however the question comes out quite often, be it in an Oracle, Firebird or PostgreSQL forum.
Well, the PostgreSQL guys took a radical approach and embedded the common solution into a datatype named SERIAL, what follows is an example aimed at the Firebird newbie.
Say you have a table like this
CREATE TABLE new_one ( col1 integer NOT NULL, col2 CHAR(10), PRIMARY KEY (col1) );
You create a generator
CREATE GENERATOR my_gen_id;
Now you want it to kick in automatically, just like an autoincrement, no need to explictly call it,
you can achieve this through a trigger:
CREATE TRIGGER autoincrementor_id FOR new_one ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.col1 IS NULL) THEN NEW.col1 = GEN_ID(my_gen_id,1); END
To retrieve the current value after an insert (if you aren't using Firebird 2.1 RETURNING clause):
SELECT GEN_ID( MY_GEN_ID, 0 ) FROM RDB$DATABASE;
Addendum:
Sometimes people might want to empty the table and reset the "autoincrement" id (a questionable approach IMHO if that value has any kind of meaning).
This can be accomplished by a simple:
SET GENERATOR MY_GEN_ID TO 1;
Where 1 is the chosen restart value.
NOTE that it will be the current value so your trigger will start with 2, you might want to set it to 0 instead of 1.
Addendum 2:
Firebird 2 introduced a new (and better) synthax fully documented in the Firebird Generator Guide.
Addendum 3:
Firebird 3.0, thanks to Adriano dos Santos Fernandes will support IDENTITY columns, so no need to use this trick anymore.
No comments:
Post a Comment