Adam wrote:

>>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.
>
>

Thank you - I don't usually pick up on hints (as my wife will attest!).

>> 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 new.id to the generator value.
>
>

Assuming I trust any manually specified id - sometimes I'm paranoid
enough that I don't even trust myself. The max() check was the simplest
way I came up with to guarantee the validity of the id.

>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.
>
>

Perhaps, probably, hopefully, should not. Being able to specify this
behaviour in a SP will make a difference in my toggling it on/off.

--
Daniel