Subject Re: Generators for auto-increment - with override
Author Adam
> Thanks very much for the responses - but they don't quite answer my
> questions. To re-state:
> Question #1: Where should I implement "generator protection" -
> before or after inserts/updates?

I think they were answered quite well already, but to spell it out in
black and white, if you choose to implement it, put it in both insert
and update triggers, I recommend before insert and before update
because it at least is incremented sooner.

> Question #2: Should I keep this "protection" in a production
> environment?

Most people have advised you against it. Certainly the way you
originally wrote it was really inefficient, but you could probably get
away with leaving it in place providing you get rid of the select
max(id) bit and just compare to the generator value.

You should not need this protection in the production environment
though. I can understand its use in a development/testing environment
when manually manipulating tables to confirm behaviour, but any
program should call for the generator value first. Perhaps you can
create the trigger and store the definition as a .sql file. Then if
you need to work on the production database, run the .sql against it.