Recursive trigger ?

Question

Post reply

Recursive trigger ?

Sql Junkie-204042

Hall of Fame

Points: 3874
More actions
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 ?

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

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)

steve smith-401573 SSChampion Points: 10889 More actions · Answer 3

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?

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 4

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

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

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 6

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

truncate both tables, insert data in both systems.

GO home.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

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

philcart SSC-Forever Points: 47795 More actions · Answer 10

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Wasn't even aware that I was that close .

Maybe I'll even get there before PASS 2007.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 12

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)