Having 2 Databases in Sync

What if both tables have this structure:
 OrderId
 OrderDate
 ShipDate
 InvoiceDatePoNumber
 Source
 Copied

Where the field SOURCE is either "A" or "B" (or whatever combination you want), indicating where the rows come from, and the field "copied" indicates if the row has been syncronized to the other database.

 Now, when Database_A is syncronized with Database_B, all records with Source="A" and copied=FALSE need to be transferred to Database_B, and as second step, all these records need also to be updated so the value Copied get's set to TRUE

You can also try to rely on replication (which is build-in in SQL Server), and schedule the replication to occur only once.

CHeers

or you may use triggers:

define this trigger on Databse_A

create trigger tr_I_TransactionsA
on Transactions
for insert,update
as
     /*Check if operation was an update*/
     if update(OrderDate_A)
          update B
          set B.OrderDate_B=A.OrderDate_A
          from Database_B..Transactions B
          join inserted A
          on B.Orderid_B=A.Orderid_A
     else if update(ShipDate_A)
          update B
          set B.ShipDate_B=A.ShipDate_A
          from Database_B..Transactions B
          join inserted A
          on B.Orderid_B=A.Orderid_A
     else if update(InvoiceDatePoNumber_A)
          update B
          set B.InvoiceDatePoNumber_B=A.InvoiceDatePoNumber_A
          from Database_B..Transactions B
          join inserted A
          on B.Orderid_B=A.Orderid_A
     else/*Operation was an insert*/
          insert into Database_B..Transactions
          select * from inserted
go

Then define this trigger on Database_B

create trigger tr_I_TransactionsB
on Transactions
for insert,update
as
     /*Check if operation was an update*/
     if update(OrderDate_B)
          update A
          set A.OrderDate_A=B.OrderDate_B
          from Database_A..Transactions A
          join inserted B
          on A.Orderid_A=B.Orderid_B
     else if update(ShipDate_B)
          update A
          set A.ShipDate_A=B.ShipDate_B
          from Database_A..Transactions A
          join inserted B
          on A.Orderid_A=B.Orderid_B
     else if update(InvoiceDatePoNumber_B)
          update A
          set A.InvoiceDatePoNumber_A=B.InvoiceDatePoNumber_B
          from Database_A..Transactions A
          join inserted B
          on A.Orderid_A=B.Orderid_B
     else/*Operation was an insert*/
          insert into Database_A..Transactions
          select * from inserted
go


using these triggers both tables from both databases are in sync in real time.

hope this helps.

xnmb

BTW, if you're only synchronizing updates, then remove the for insert definition and leave only the (for) update then
ommit the insert operations.

xnmb

Or better yet, why not have a single database for both front-ends?

xnmb

xnmb-

I have experience with SQL server but I have never don trigers. Can you guide me a little beat on that. Do I write it in a Stored Proc or is it a different thing??

Thanks

xnmb -
To answer your last comment, we have to use two databases, since one of them is created by another company and we can not do any changes on it. When they have updates on the database everything gets over writen.

xnmb -

Ok....

So triggers are not part of Stored Procs.....
So when you say "run this in database A" where do you exactly mean? Should I run it in Query Analyzer??

Thanks again

I figured out where to do the triggers.
I Opend the database clicked on my table and then manage triggers and I pasted your code there. Then I Inserted a value in one of the tables and nothing happend....
so how do I execute the triggers???

you dont invoke triggers manually. triggers "fire" when certain operations are performed against the table on which you defined your trigger(s).

create trigger tr_I_TransactionsA
on Transactions <--This is the table on which the trigger is defined(Transactions table)
for update <--This is the operation, when performed against the table, will fire the trigger
as
...

xnmb

Just one last question.....

How do I get this to work with INSERTS and UPDATES together instead of UPDATE only?
Also would this work if I have my key field in my table as Auto Increment Identity?

Thanks

AutomaticSlim:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.