pwalter83 (7/24/2013)
Hi,I am very new to SQL and really dont know how to phrase my question.
There are 2 tables linked through a primary key and if the values in one table change, the corresponding values in another table should be changed and reflected accordingly.
Does someone know what logic I need to apply for this to work ? Do I have to create a primary key-foreign key relationship and then create a trigger on the other table on which the values need to be updated ?
The values in the table will be changed through a webpage.
Any ideas would be appreciated.
Thanks.
it sounds like you want to echo the same value in two places, is that correct? so if i change one table, it copies the same value to a related record in another?
if the above assumption is true, here's my thoughts:
This is breaking the whole normal forms rule: if a value is in one table, and there is a relationship between the two tables anyway, you can always join the tables together to get the value when it needs to be displayed in reference to the second table, so you want to try and avoid that.
so i'd say it's better to drop the column in the second table, and get the value in a query;
you could also create a view with that query above, to make it easier to access;
another possibility is to replace the column in table 2 with a calculated field, and the calculated field calls a user defined function that effectively gets the value you want from the other table, based on the join/relationship between the two.
because that UDF would be scalar, it would slow down access to the table if there is a ton of rows, which makes the query above idea more palatable in my eyes.
finally, if you are painted into a corner, and just have to do it, you could create a trigger that copies the value to the other table for insert and update.
Lowell