> >
> This assumes that either the generator value or the specified key

is

> still greater than any existing record in the table. That may be a
> perfectly reasonable assumption - but it's not a guarantee.
>

Daniel, I can not see your problem here:

Put simply, a record is inserted either:

1) sometime before now
2) sometime after now

Create your trigger which compares NEW.ID to the current generator.
This will 100% guarantee you are protected against any future record
being inserted after now causing the problem (part 2 all good).

So the only possible problem is if an existing record in your table
has a key that is greater than your generator.

Create a temporary stored procedure to correct any past instances. I
posted this earlier this morning. Something like

select max(id)
from tableA
into :maxID;

Execute procedure SP_ProtectGenerator('gen_tableAID', :maxID);

Now execute this procedure. Now you have problem 1 covered too. There
is no data currently in the table with an ID greater than your
generator. Because of the trigger, no future data can be greater than
the ID either.

So you are protected just as well as if you used select max in your
trigger without the horrendous overhead that statement would cause.

You can now drop the temporary stored procedure you made to fix
problem 1.

Adam