How to implement real-time database Synchronization Through triggers

The test environment is as follows: SQL Sever 2000 database, remote host name: xz, User name: SA, password: None, Database Name: Test

When creating a test table, note that the primary key cannot be used as the ID column because normal updates cannot be performed.

Then create a test table on the local machine. Note that the same table creation operation is also required on the remote host, but the trigger is not written.

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 synchronization 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_cmdshell ''net start MSDTC '', no_output/" ', no_output

-- Start the MSDTC Service of the Local Machine

Exec master.. xp_mongoshell 'net start MSDTC ', no_output

-- Perform distributed transaction processing. If the table uses the ID column as the primary key, you can 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

-- Data insertion Test

Insert into test

Select 1, 'A'

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 + '_ 100' where ID in (123)

-- Display the test result.

Select * from test a full join

OpenRowSet ('sqloledb', 'xz '; 'sa'; '', test. DBO. Test) B on A. ID = B. ID