Real-Time Database Synchronization using triggers

-- -Real-Time Synchronization of two tables:
-- Test environment: SQL2000, remote host name: xz, User name: SA, password: None, Database Name: Test

-- Create a test table and do not use the ID column as the primary key because normal updates are not allowed.
-- Create a test table on the local machine. Create the same table on the remote host, but do not write the trigger.
If   Exists ( Select   *   From DBO. sysobjects Where ID =   Object_id (N ' [Test] ' ) And   Objectproperty (ID, n ' Isusertable ' ) =   1 )
Drop   Table   [ Test ]

Create   Table Test (ID Int   Not   Null   Constraint Pk_test Primary   Key
, Name Varchar ( 10 ))
Go

-- Create a synchronous trigger
Create   Trigger T_test On Test
For   Insert , Update , Delete
As
Set Xact_abort On
-- Start the MSDTC Service of the remote server
Exec Master .. xp_mongoshell ' ISQL/s "xz"/u "sa"/P ""/Q "Exec master .. xp_mongoshell '' Net start MSDTC '' , No_output" ' , No_output

-- Start MSDTC Service on the local machine
Exec Master .. xp_mongoshell ' Net start MSDTC ' , No_output

-- For distributed transactions. If the table uses the ID column as the primary key, use the following method:
Begin   Distributed   Transaction
Delete   From   OpenRowSet ( ' Sqloledb ' , ' Xz ' ; ' SA ' ; '' , Test. DBO. Test)
  Where ID In ( Select ID From Deleted)
Insert   Into   OpenRowSet ( ' Sqloledb ' , ' Xz ' ; ' SA ' ; '' , Test. DBO. Test)
  Select   *   From Inserted
Commit   Tran
Go

-- Insert data test
Insert   Into Test
Select   1 , ' AA '
Union   All   Select   2 , ' Bb '
Union   All   Select   3 , ' C '
Union   All   Select   4 , ' Dd '
Union   All   Select   5 , ' AB '
Union   All   Select   6 , ' BC '
Union   All   Select   7 , ' Ddd '

-- Delete data test
Delete   From Test Where ID In ( 1 , 4 , 6 )

-- Update Data Test
Update Test Set Name = Name + ' _ 123 '   Where ID In ( 3 , 5 )

-- Show test results
Select   *   From Test Full   Join
OpenRowSet ( ' Sqloledb ' , ' Xz ' ; ' SA ' ; '' , Test. DBO. Test) B On A. ID = B. ID