Subject Re: [IBO] Autoinc field value unknown after insert+post+commit
Author Paul Vinkenoog
Hi jrp_public,

> I have a table with an autoincrementing field which I use as a
> primary key. The autoincrementing is the common trigger + generator

Good. Make sure that the trigger goes like this:

if ( new.MyPKField is null )
then new.MyPKField = gen_id( MyGenerator, 1 );

and NOT just like this, unconditionally:

new.MyPKField = gen_id( MyGenerator, 1 );

It will later become clear why.

> After posting a newly-inserted record, the value of the autoinc
> field is NULL.

After posting, the trigger fires so the field is no longer NULL *in
the database*, but IBO doesn't know what value has been assigned.

> If I refresh the dataset to get that value then the record pointer
> will point somewhere else (it doesn't listen to
> RefreshAction=raKeepDataPos).

That's because IBO uses the PK to determine the identity of the
record. But the PK value has just been changed in the trigger, so IBO
can't find that record back.

> All I want is very simple: After inserting (+post +commit) a new
> record, the record pointer should be pointing at the record I have
> just inserted, and the value of the autoincrementing / triggered
> field is known.

The trick is: tell IBO to call the generator and assign the generated
value to the PK field. Select the query on the form and look in the
Object Inspector for GeneratorLinks. Click on the "..." and type this
line in the empty list:

(replacing my sample names with the real ones of course!)

That's all. From now on, as soon as you insert a new record, IBO will
fetch the generator value and write it to the PK field. So, IBO now
*knows* the PK value before posting, and will be able to identify the
record later.

Now it should also be clear why the condition in the trigger is
important: if it's not there, the engine will overwrite the value
written by IBO, the PK value changes, and IBO won't recognize that
record as the same one from before.

BTW, a dataset refresh shouldn't be necessary (unless you also needed
it for other reasons).

Paul Vinkenoog