Subject RE: [firebird-support] Complex generators?
Author Alan McDonald
i have data that is strucured something like this:
AB 1
AB 2
AB 3
CD 1
CD 2
AB 4
(ID field grows from 1 up, for a single TYPE value), and I'm having
problems implementing data entry in the database. I was thinking of
using a autincrement-type trigger like this:

genname = 'GEN_' || new.type; = gen_id(:genname, 1);

The problem is, the database doesn't like it, and complains of a
syntax error at the gen_id() call. I've tried "SELECT gen_id
(:genname,1) INTO somevar" but it doesn't work.

Is there a (nice) way of implementing this behaviour? My ideal case
would be without using generators, because the TYPE field is also
dynamic (and CREATE GENERATOR doesn't work inside a trigger, also...)

Your are right - you cannot construct dynamic constructor names at the
But before we can suggest some alternatives, maybe you should tell us if
having continuous ID fields is important. I ask because if you use triggers
in this simple way, then transaction rollbacks will cause lost generator
values since generators are not subject to transaction control.

Without knowing more I would suggest that you use generators to create a
unique record id which has no business meaning whatever. then you can
implement something client side and/or server side to do this other job or
maintaining your record types