Hello,

In a Database I have a A table "Orders" with 2 columns
("country_invoiceaddress", and "country_shippingaddress") that are
foreign keys pointing to the primary Key of table Countries.

Now it should be possible, that if the key of a country is changed the
rows in table Orders get changed accordingly. I cannont do that with "on
update cascade" for both columns because only one cascading path is
allowed in sql server 2000.

So the best solutions would be using a Trigger, but I don't know
exactely how.
When the primary key of a row of table Countries is changed, how can I
get the row (old value and new value) which was changed to update the
right rows in table Orders?
Or can I only update all Orders if table Countries gets changed? - that
would be inperformant!

Thanks
Ferdinand

On Mon, 26 Nov 2007 15:59:20 +0100, Ferdinand Zaubzer wrote:

Show quoteHide quoteHi Ferdinand,

In an UPDATE trigger, the deleted and inserted pseudo-tables expose an
image of the affected rows as they looked before and after the update.

If an update affects only a single row, finding out the old and new
value of the primary key is trivial, since there is only one row in
inserted and deleted. But for a multi-row update, this becomes a real
problem. The only 100% reliable method is to use some other column (or
combination thereof) that is constrained to be unique and that is not
allowed to be affected by the same UPDATE command. If there is no such
column (or column combination), you'd have to disallow updates that
affect the primary key column to sets of more than a single row at a
time.

If you need more help actually writing the trigger, then please post
CREATE TABLE statements and INSERT statements for some sample data.

SQL 2005 Express to SQL Server 2000
Restoring a column in a table
database and tlog backups - MSSQL 2000 & MSSQL 2005
Live server running out of space - Can't reduce Transaction Log!
Copy views from one database server to another