Post reply

Recursive trigger ?

  • August 1, 2007 at 1:03 pm

    #197128

    I have a table with the same structure on two different servers. If any update is done on Server A (SQL 2000), the data needs to be copied over to Server B (SQL 2005 Express)  and vice versa.

    This is a small table with 10 rows and 10 columns. I plan to implement this using triggers and DTS.

    Will this be a recursive trigger ? What is the best approach ?

  • August 1, 2007 at 1:24 pm

    #723989

    Sounds more like you need bi-directional replication.

     

    I'm saying that without having done anything in replication, but it think it's worth a look for you.

  • August 1, 2007 at 8:36 pm

    #724052

    I'm thinking that it's a bad idea to use a trigger for this... what happens when one of the servers is down for some reason?  You got it, the other server becomes useless because the trigger on the other will fail.

    Even if you get around that by having the trigger sample the existance of the other server and doing nothing if it's not up, how will you resync what's been done on one with the other?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 2, 2007 at 1:21 pm

    #724310

    Won't the triggers also, if not written carefully, result in constantly tripping each other? Server A gets an update, so its trigger updates Server B. Server B now has an update, so its trigger now updates Server A. Ad nauseum?

  • August 2, 2007 at 1:56 pm

    #724327

    Don't use triggers. Use a identical stored procedure on each server to perform the insert. When your app writes data to the table, it does so by executing the sproc with the data for the table and an extra parameter which has the value zero. The stored procedure inserts the data and, if the extra value is zero, calls the sproc on the other server, but with the value for the extra parameter of one. When the value is one, the sproc skips the execution of the other.

    The sproc logic would be:

    create sproc( 
    @TableData1, ..., @Replicate )
as
  --Insert @TableData values into the table.
  ...
  --Finally
  if @Replicate = 0
    exec ServerB.db.owner.sproc(@TableData1, ..., 1)

    Of course, you would still have a synch problem if one of the servers go down for a time. Maybe you could write the data to a holding table. If it can't reach the other server, it inserts into the holding table. If it can reach the other server and the holding table has entries, lock the holding table, pass the entries along to the other server, remove them from the holding table, unlock the holding table and then pass along the current data.

    Or you could mirror the databases. I don't know anything about that so you would have to ask a DBA for details.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • August 3, 2007 at 8:18 am

    #724519

    Please look into Merge Replication.  It is designed to do exactly what you are attempting.  It is relatively easy to setup by using Books on line.  It will gracefully handle any connectivity issues between servers as well.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • August 3, 2007 at 1:33 pm

    #724629

    Sorry for replying late.

    I got to it to work using Tomm's solution.

    I prefer not to use Replication - the volumn of data is very low (10 rows) and updates are rarely done  (twice a month).

    Thanks all.

     

  • August 3, 2007 at 1:37 pm

    #724630

    truncate both tables, insert data in both systems.

     

    GO home.

  • August 3, 2007 at 7:59 pm

    #724687

    That's basically what Tomm's solution does

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 6, 2007 at 8:30 am

    #724841

    That happens... I don't ahve time to reread the whole post everytime I answer... sorry if anyone got offended by that "double" post .

  • August 6, 2007 at 8:36 am

    #724843

    Not to worry, we all know you're pushing for your 10,000th

    --------------------
    Colt 45 - the original point and click interface

  • August 6, 2007 at 8:39 am

    #724845

    Wasn't even aware that I was that close .

    Maybe I'll even get there before PASS 2007.

  • August 6, 2007 at 6:34 pm

    #724982

    Go Remi, GO!

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 13 posts - 1 through 12 (of 12 total)

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