You are safe then. As long as it is correctly written, you will not
have any noticable performance loss.

That said, you should write the protectgenerator sp by passing in the
generator name and a value.

Then say your employee table before update trigger could call:

SP_Protect_Generator('GEN_EmployeeID', NEW.ID);

The SP would then query the generators maximum value, and if the
passed in value was bigger, then the generator is incremented.

If I was you though, I wouldn't even tell my users or application
developers (except those that need to understand this code) that this
feature is implemented. It could encourage lazyness on their part. In
fact I would have a separate version of the database for the testing
which doesn't have this protection (simply comment out the SP code)
just to make sure no code relies on a generator protector. Your
database needs to be able to maintain consistency without this
function, all we are doing here is covering ourselves if we forget to
increment the generator. The protection is not bullet proof
(technically possible for insert to fail due to duplicate entry in
some race conditions) so you dont want to rely on it as normal
behaviour.

> >
> Point well taken. My intent for trigger-called SP's is to use non-

table

> modifying SP's (other than perhaps changing a generator value), and

use

> the return value in the triggers.
>
> --
> Daniel