At 07:44 PM 3/02/2004 +0000, you wrote:

>Hi all,
>
>Is there a way to swap primary keys in two rows? I have a table like
>
>CREATE TABLE MAILBOX (
> MAILBOX_ID BIGINT NOT NULL,
> OWNER BIGINT NOT NULL,
> MAILBOX_TYPE VARCHAR(10),
>
> PRIMARY KEY (MAILBOX_ID),
> UNIQUE (OWNER, MAILBOX_TYPE)
>)
>;
>
>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?
or
3. What else?

>If the value doesn't yet exist, no problem of course, but if it exists,

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'd like to swap it with the existing value, or set the existing value to
>a new ID from a generator. How would I do this in an UPDATE trigger?

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

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

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

>Will other tables that have a FK to MAILBOX_ID still CASCADE properly?

It depends on the timing of the creation of the foreign records. That's
not clear from your problem description.

/heLen