Auto update fields in different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Auto update fields in different tables

Author

Topic

Leonius
Starting Member

7 Posts

Posted - 2011-08-24 : 12:31:08

1st question, Ok two tabes, TB1 and TB2, TB2 is linked by FK to TB1, when TB1 has updated values inserted I want TB2 to update at the same time, how do I do this?.

2nd question, TB1 stores a value an identifier and a date,

TB2 is to store the historical maximum values from TB1, so there is value, identifier and date,

If TB1 has a value on a date that exceededs the stored previous maximum value in TB2 (identified by the corresponding identifier) then the value and the date of the value in TB2 need to change when this happens.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 12:34:03

for first question, write an update trigger on TB1 to update TB2 also

again this logic can also be implemented inside trigger

Not sure what should value change to? can you illustrate with an example?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Leonius
Starting Member

7 Posts

Posted - 2011-08-24 : 12:52:13

The max values table TB2, say a stored max value is 500 and the date is 01/01/2011 and the identifier is ABC, TB1 has the same values as they were passed by TB1 to TB2.

A new value is recorded in TB1 that exceeds the previous value, 600 and a new date that the value was entered 02/02/2011 identifier ABC,

What I want to do is get TB1 to check the value in TB2, if the value exceeds the value in TB2 then that value and new date should replace the old value and date for identifier ABC.

There would be identifiers as there would be multiple max values stored for differing items.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 13:32:15

it should be like


CREATE TRIGGER Trig_Update
ON TB1 FOR INSERT
AS
BEGIN
UPDATE t2
SET t2.value=i.value,
t2.date=i.date
FROM INSERTED i 
INNER JOIN TB2 t2
ON t2.identifier = i.identifier
WHERE t2.value < i.value
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources