|Subject||Re: Generators for auto-increment - with override|
> This assumes that either the generator value or the specified key
> still greater than any existing record in the table. That may be aDaniel, I can not see your problem here:
> perfectly reasonable assumption - but it's not a guarantee.
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
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