Hi

h_urlaf 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. If the value doesn't yet exist, no
>problem of course, but if it exists, 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? Will other tables
>that have a FK to MAILBOX_ID still CASCADE properly?
>
>
>

Sorry, but am having difficulty understanding what you're trying to do.

in before update trigger you could do something like this

/* select the specific value we set in the table*/
select mailbox_id
from mailbox
into :temp_mailbox_id;

/* check if the specific value exist or not, if it exist change the
existing value to a new ID from a generator */
if (temp_mailbox_id is not null) then
begin
new.mailbox_id=gen_id(generator_mailbox_id,1);
end

Hope this helps. Even if this solves your problem if believe there is a
better solution, which means it is faster, uses less resources.

Cheers
James