>
> Although this is a common pratice, I've run into issues many times. If
> you do provide the value, it might clash with autogenerated one in the
> future. So I mostly rewrite the triggers like this:
>
> create trigger...
> begin
> if (new.microarrayid is null) then
> new.microarrayid = gen_id(microarray_id_gen, 1);
> else
> check if new.microarrayid is bigger than
> the generator value
> and move the generator up to it
>
> Well, not so simple, but effective.

Not always a good practice though.

A common use for this technique is if you are maintaining a master
detail relationship in a clientdatset or equivalent. You can fire off
the appropriate queries as required to pull in the true PK values to
be used.

Using your trigger, the relationship would be broken upon insert. But
you are right that if you choose a random PK value, that is going to
give you a PK violation.

So put in place a development procedure. If you are filling a PK with
a value, then the value you choose must originate from the respective
generator / sequence. Unless someone resets the generator, you will
never have an issue with a duplicate, and I do not think the
development procedure is unreasonable (in fact it is highly logical).

Adam