Subject Re: Generators for auto-increment - with override
Author Adam
> >
> Assuming I trust any manually specified id - sometimes I'm paranoid
> enough that I don't even trust myself. The max() check was the
> way I came up with to guarantee the validity of the id.

If inside the before insert and before update trigger, then the
protection will be met whenever you add or change a record. What that
means is that the record with the highest ID will have called the
generator protection.

For example, if your table had a maximum record with an ID of 1000 in
the table (from a prior insert), and you are inserting a record with
an ID of 900, when you call the generator, it will be at least 1000
because that first record would have said ProtectGenerator
('tableA',1000) or something like that. Your second insert would call

The only possible problem is if the first record was inserted before
this protection was implemented. To fix this, create a temporary
stored procedure that does something like

select max(id)
from tableA
into :maxid;

ProtectGenerator('tableA', :maxid);

select max(id)
from tableB
into :maxid;

ProtectGenerator('tableB', :maxid);


Run this procedure once then drop it.

Unless you have a decending index, selecting the maximum value is too
inefficient to call on every insert. You have triggers and your
stored procedure to fix any future generator value. You have just run
this temporary SP to fix and current or past values.