At 12:24 AM 07-08-02 -0400, you wrote:

>I'd rather do it right, though, but I don't follow what you are getting at
>below. Going back to Roman's original reply I understand that I can get an
>auto-incremented value from the generator by using a command like:
>
>SELECT gen_id(my_generator, 1) FROM RDB$DATABASE
>
>But in the code below wouldn't I want the trigger active AFTER insert so
>that if I insert the key I get from the code above, the trigger generated
>number won't be overwritten by the number being manually inserted?

No, you misunderstand the timing of triggers. You fire BEFORE triggers to
affect the data you are inserting (or updating, in the case of BU). Once
you get to the AFTER phase of an operation, the ".new" values in the record
are read-only. You fire AFTER triggers to affect data in other tables.

The code I gave below makes it so that the "autoinc" BI trigger will fire
if and only if a value isn't supplied. If you do

insert into atable(PKcol, col1, col2)
values (null, 'something', 'somethingelse')
the trigger will fill PKcol with a new value from the generator.

If you do
insert into atable(PKcol, col1, col2)
values (99, 'something', 'somethingelse')
the trigger will fire but it won't call the generator.

>Thanks for the insights, this has been very helpful and my apologies to the
>group for this firebird-java question morphing into a an ib-support
>question.

Yep. If you want to know any more, take it to ib-support.

As a matter of interest (since this is a *driver* list) different drivers
provide "more or less" support for generators. The best I know is IB
Objects (components for Delphi). It has two useful things for generators
which totally solve the problems. One is the GeneratorLinks property on
its dataset classes. The way it's implemented, you just have to supply the
name of the column and the name of the generator into the property string
and the driver does the rest, e.g.
MyColumn=MyGenerator. (This is an array of strings so it can handle
multiple generator columns in a single dataset...) For occasions such as
the one you were asking about, it implements a Gen_ID() function that
returns the latest value of the generator back as an Int64.

It would be a real asset if the JDBC driver were able to implement those
two encapsulations; but whether the standard provides for it is something
else.

Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
______________________________________________________________________