Hi Jim,

Jim Harlindong wrote:

>
> > I have a generator and a before trigger to assign it to the PK of a table.
> > I found that IBO incremented the generator before inserting and
> > since I did
> > not include the field involved in the INSERT statement, the
> > generator on the
> > server incremented the generator again (since the field is null).
>
> correction: a "before insert" trigger.

since i don't clearly see what's going on there, here's some information
that will probably get you ahead:

- first of all IBO relies on some way of *uniquely* identifying your records.
You already have an ID populated by a generator, imo the best way to go here.
(the IBO Bookmarks are simple strings which are actually made up of the PK)

- in your Before Insert Trigger, you should use something like

if ((new.id is null) or (new.id=0)) then /* and only then! */
new.id = gen_id(mygenerator,1);

if you omit the if clause, then any new record will be assigned a freshly
generated id, even if it has one already. (the (new.id=0) check is optional,
i always use this because for an ID, 0 is logically equivalent to NULL in my
thinking... since i use memory objects that have their ID stored as an integer
property - and in delphi there is no NULL for properties, just 0...)

> Also this caused an incorrect value of my PK on the client / IBO since it is
> using the value that it has obtained from the generator prior to the server
> incrementing it. It did not re-get the value from the server.

so it seems you don't have the if.. in your trigger... The funny thing is that
if you would use *only* IBO with a properly set up GeneratorLinks entry to
insert
records into the table, you would not need the Before Insert Trigger at all.
Step By Step:

- you issue a Post in your client app. IBO sees the GeneratorLinks entry and
fetches the next Generator Value from the server (select gen_id... from
rdb$database). So, before it even really posts your record, IBO already
*knows*
the generator value (and can build a bookmark for that).

- then the actual Insert on the server happens. If you have a) a trigger like i
described above or b) no trigger at all, the record is inserted with the
generator value that IBO "prefetched". (and don't worry about double
generators,
they run outside of transactions in IB server and are therefore absolutely
multi-user safe)

- Since you can have Before Insert triggers that actually modify the columns,
the next thing that'll happen is that IBO fetches exactly the record that was
just inserted back from the server... which is easy for IBO because it knows
the PK value already (select ... where ID=the_gen_i_just_got). You'll notice
that when you insert a record in a TIB_Grid, after posting the grid will not
flicker. That's because it has all displayed records in the client buffer
and re-fetches (and re-paints) only the new one. (which is the most efficient
way i can think of :-)

So you can completely rely on IBO to manage the Generator increment, you just
have to make sure that your Trigger doesn't "ruin" IBO's insert/generator logic.
(i came to using those Before-Insert-generated IDs only as a security mechanism
for the rare cases where i *don't* use IBO to insert, so the recs still get a
valid id then...)

hth,
fingerman