Triggers in sql server

[Mark Commons]

I have two tables

Table 1 has columns
boat price currency normalprice
fred 1000 Euro ?????

Table 2 has columns
currency exchangerate
Euro 1.2
Pound 1.5

What I want is to create a trigger that when I Insert or Update a row in Table 1 it looks up the right currency in Table 2 and multiplies the price in Table 1 by the exchangerate in Table 2 and stores it back in the normalprice column in Table 1.
I've been having problems in that it updates all the rows in the database not just the one I'm inserting or updating.
Any help would be much appreciated!

[Stephan Deblois]

You have to use the temporary table inserted:

update table1
set table1.normalprice = table1.price * table2.exchangerate
from table1,table2,inserted
where table1.boat = inserted.boat and
table2.currency = table1.currency

the table 'inserted' contains only the record updated or inserted in a trigger.
when you link table1 to the 'inserted' table you are updating only the good record.

hope this help

bye

------------
Mark Commons at 3/31/99 10:24:50 AM

I have two tables

Table 1 has columns
boat price currency normalprice
fred 1000 Euro ?????

Table 2 has columns
currency exchangerate
Euro 1.2
Pound 1.5

What I want is to create a trigger that when I Insert or Update a row in Table 1 it looks up the right currency in Table 2 and multiplies the price in Table 1 by the exchangerate in Table 2 and stores it back in the normalprice column in Table 1.
I've been having problems in that it updates all the rows in the database not just the one I'm inserting or updating.
Any help would be much appreciated!