Post reply

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

  • 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.

  • January 22, 2018 at 2:30 pm

    #1976823

    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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 22, 2018 at 2:33 pm

    #1976825

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

  • January 22, 2018 at 3:05 pm

    #1976831

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • January 23, 2018 at 2:44 am

    #1976865

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

  • January 23, 2018 at 10:48 pm

    #1977043

    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 ?

  • January 24, 2018 at 3:04 am

    #1977063

    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

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

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