The example you posulate will cause problems with any form of auto generated
date.

It is clear that the problem exists because when you want your specific Id
you are in fact treating business data as a row identifier. Business data
can only be a row identifier as far as humans (carbon life forms) are
concerned and thus are just another info value for the data.

I would suggest that you modify your table to

RowId integer not null,
CarbonId integer,
Somethingelse integer,

Primary key RowId

then
before insert
as
if RowId is null then RowId = Gen_Id(MyGenny, 1);
if CarbonId is null then do what ever you want

end

When user wants to find something first search CarbonId then if not found
search RowId.

In real life users should not know about or have anything to do with Row
identifiers - they are only for the machine.

You will find this sort of problem when designers use phone numbers (after
all phone numbers are unique) as RowIds.


mick

> The question is: why are you creating your own ID in
> the first insert. Without it, there is no spoon. Sorry: problem.

Because we have a situation where the IDs can usually be 'random' as
long as they're unique, but in some instances we want a specific ID.

> If you want a generator to generate IDs, then do so. Always.

Then what is the point of things like

if (new.ID is null) then new.ID = gen_id(g1, 1);