Trigger for data synchronization between two servers

First, configure two computers first, for detailed steps see http://support.microsoft.com/kb/839279/en-us

 

Second, create a connection server,CodeAs follows:

Exec sp_addrole server 'srv2', '', 'sqloledb', '192. 168.0.14'

Exec sp_add1_srvlogin 'srv2', 'false', null, 'sa ', 'sa123456'

Note: These two stored procedures are defined by the system and can be used directly. Parameter description: 'srv2': the alias of the linked server. With this alias, You can reference it to the linked server. There is an empty string in the middle. I don't know what it means, so don't worry about it. "Sqloledb": a mechanism provided by Microsoft. You just need to go down directly. '1970. 168.0.14 ': IP address of the connection server. 'Sa ': the user name used to log on to the linked server. 'Sa123456'

The password used to log on to the server.

Third, create a trigger:

Create trigger t_test on ordertext

For Delete

As

Set xact_abort on

Begin distributed tran

Delete from srv2.csfoods. DBO. foodtype1

Where typeid in (select ID from deleted)

Commit tran

Fourth, test.

Here, we delete a piece of data.

Delete from ordertext where id = 1

When this data item is deleted, the data in the foodtype1 data table in the csfoods database on the srv2 server will be deleted immediately.

 

 

Note: yesterday, our manager asked me to create a trigger to synchronize and delete the data on the two servers. I was crazy about Baidu and Google on the Internet, but I found that manyArticleI did what they said, but I still can't. I keep holding my mistakes. I looked at it carefully and the code should be correct. The problem must be related to the configuration, that is, the configuration of the two computers on my side is incorrect. Think about it. These configurations are all set at according to the articles on the Internet. Later, I found an article in English, and I simply got it done according to the configuration method described above. Later I thought about it. Many configuration methods on the Internet are not scientific and there are too many configuration items. As for the specific configuration, I have provided a link at the beginning.

 

If you want to access another database on the same server, this is simpler. Method 1: add the linked server according to the preceding SQL code, but you can directly access the server without configuring those items.

Method 2: It is very simple and can be accessed directly using the database name. For example, select * From northwind. DBO. Orders. The second method is recommended.