Re: updating primary key

From: Dan Clamage <clamage_at_mime.dw.lucent.com>
Date: 1997/10/14
Message-ID: <01bcd8ab$734796d0$54110b87_at_clamagent>#1/1

Updating primary keys is a bad idea, as you have discovered. The orderly thing to do is to, in one transaction:

1) Insert a new row with the new key (duplicating the rest of the row data)
2) Update the foreign keys in other table(s) to the new key value
3) Delete the old row

If any part of this fails, roll the whole thing back. Query on the new key to get the row just inserted. You can do this whole thing with a stored procedure, called from the trigger.
- djc

Debbie Nein <neind_at_ci.portsmouth.va.us> wrote in article <01bcd80e$26aac9c0$7202070a_at_neind.visi.net>...
> I have 2 tables A & B. Table B has a foreign key to Table A
> (master/detail). I need to update a column that is part of the primary
 key
> in Table A, and I would like this to automatically update the foreign key
> in Table B. (like an update cascade, if it were possible)
> I tried to create row and statement database triggers using a package,
 but
> I got a mutating trigger.
> Also, I will need to automatically query with the new values.
Received on Tue Oct 14 1997 - 00:00:00 CEST