Create a update script to keep 2 columns in sync

Question

So i have 2 columns i need to keep in sync with each other, they are each in a separate table. Based on 3 possible conditions i need to insure that both are in sync/equivalent to each other.

Reason for this is that we have new requirements within our application and that new addition cannot simply over right the other column as older legacy systems are dependent on that original column.

So here is a rough example of what is needed.

Table #2 is the new master, so in a perfect world, we would just insure that Table #1 has the appropriate dates based on the values from table #2 "value3" column. BUT because we have older systems that still have access to update value3 column in table #1, we are trying to create a script in SQL that we can run daily, based on a trigger or whatever to help keep the old column in sync with the new column at the same time keeping the business rules in place.

So this update would need to go both directions in the sense that we know that if table#2 value = 2, the old value should be 1/1/2035 no matter what, NOW if the old table#1 value = 1/1/2035 BUT table#2 has 8, that is incorrect, so in this case the new column value needs to be updated to =2.

Does that make sense?

Because the old column is a "Date" type and the new column is "Number" which corresponds to a value/description this will allow the interface to be more flexible.. So this is where it gets complicated and why im asking here.. since conditions need to be checked to make sure the values are following the business rules in place, wasn't sure how to efficiently maintain these two columns in sync.

Answer 1

I am wondering if you can create a trigger to auto update these columns after they are inserted.

Answer 2

Hey Cubangt,

as Riaon has already written, i guess two after update triggers should be created.

for example as follow for Table 1

CREATE TRIGGER 
	[TR_U_Table1_sync_value3_to_Table2_value3] 
ON 
	[dbo].[Table1]
FOR
	UPDATE NOT FOR REPLICATION
AS
	/*
	on Update Table1.value3 
	value would be synced 
	to Table2.value3
	*/
	UPDATE
		[dbo].[Table2]
	SET
		[value3] = CASE WHEN [a].[value3] = {ts'2035-01-01 00:00:00.000'} THEN 2 END
	FROM
		[dbo].[Table1] [a]
		INNER JOIN inserted [b] ON [a].[value1] = [b].[value1]

GO

But be aware.

• what happens when rows are deleted, should these also be deleted on the opposite side? Then you need also an Delete Trigger
• what happens when new rows are inserted? If these have to be inserted on the opposite side you need also an INSERT Trigger
• is it possible to update more than one value with one statement, then you have to use an update join as shown above. 
• Please check the correctness of the case statement

regards

Michel