Update Table A when table B columns are updated and vice versa

Question

Update Table A when table B columns are updated and vice versa

komal145

SSCrazy Eights

Points: 9918
More actions
January 22, 2018 at 2:21 pm

#402866

How do i Update Table A columns when Table B is updated. Also I need to update Table B columns when Table A is updated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246992 More actions · Answer 1

komal145 - Monday, January 22, 2018 2:21 PM
How do i Update Table A columns when Table B is updated. Also I need to update Table B columns when Table A is updated.

Change the underlying UPDATE procs accordingly.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

komal145 SSCrazy Eights Points: 9918 More actions · Answer 2

This update should be automatic. Thinking of trigger on table A and table B but that will be infinite loop .

ScottPletcher SSC Guru Points: 101131 More actions · Answer 3

Not necessarily. You can use CONTEXT_INFO() to pass a "flag" to the trigger to "tell" it not to update. You might also look into replication.

Below is a sample trigger for tableA; tableB would be similar, but with 0xAA and 0xBB swapped.
If CONTEXT_INFO() is being used for other values, you will need to adjust the code to allow for retaining existing CONTEXT_INFO data

CREATE TRIGGER xxx ON dbo.tableA AFTER UPDATE AS SET NOCOUNT ON; /* check to see if tableB trigger has updated tableA */ IF SUBSTRING(CONTEXT_INFO(), 1, 1) = 0xBB SET CONTEXT_INFO 0x00 RETURN; END /*IF*/ SET CONTEXT_INFO 0xAA /*indicate that trigger on tableA has processed its UPDATE trigger.*/ /* add code here to update columns in tableA */ GO

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Asim Yousaf SSCertifiable Points: 5704 More actions · Answer 4

you can use Service Broker (Asyncronous) by creating triggers on both table

subramaniam.chandrasekar SSCarpal Tunnel Points: 4223 More actions · Answer 5

komal145 - Monday, January 22, 2018 2:33 PM
This update should be automatic. Thinking of trigger on table A and table B but that will be infinite loop .

Please post any example list of records for this ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

komal145 - Monday, January 22, 2018 2:21 PM
How do i Update Table A columns when Table B is updated. Also I need to update Table B columns when Table A is updated.

Have you considered using an indexed view?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden