--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> >Most of the time the MAILBOX_ID will be auto-assigned in an INSERT
> >trigger, but we now need a way to set the ID for some of the rows
> >to a specific (still unique) value.
>
> What does this mean?
>
> 1. Sometimes the mailbox_id gets generated in a trigger and
> sometimes Joe User invents his own?
> or
> 2. The mailbox_id always gets generated in a trigger but sometimes
> Joe User is allowed to change it to something else?

Both, actually. In most cases so far, the actual mailbox_id is
generated by a trigger. But we're now seeing requests where the
application admin wants to set these IDs to company-specific unique
values (like the users' telephone number) so they can be remembered
more easily. Since we have existing deployments, I want to enable the
admin to change these numbers while still enforcing uniqueness; during
the change the admin may have to change the ID to one that had
previously be assigned to another row by the trigger.

> if it is generated in a trigger, it can't exist already. So you
> seem to be saying that your client application has an Insert
> operation that might really be an update operation?

I don't think so (if I understand you correctly). As long as all IDs
are generated, no problem. It's when the admin wants to change them to
company-specific that I run into problems. I figured if, for example,
he wants to assign the number 5073 to a row, and it's already taken,
swapping the IDs of these rows (with cascading updates to other tables
that reference them) could save me some hassle.

> You can't fire an UPDATE trigger from an Insert operation. If you
> need to have an operation that is conditionally an update or an
> insert, depending on whether the unique ID exists or not, then don't
> use a trigger for it. Triggers are tied to the actual operation
> (Before Insert or Before Update).

It's more like when the user executes an UPDATE that sets the mailbox
ID, the trigger could try to resolve the potential conflict.

> Have your client's Insert operation invoke an executable stored
> procedure that makes the existence test and performs the insert or
> the update accordingly.

Ah yes, of course. Excuse my ignorance, I'm still getting my head
around 'advanced' database concepts like triggers and SPs as I'm
trying to migrate our existing product away from Access.

> Make sure your generation code in the Before Insert trigger tests
> the new.mailbox_id for NULL before firing the generator.

Yes.

Thanks for the info,
Emiliano