Implement the instant synchronization scheme of the database through the trigger
First, achieve the purpose
In order to achieve data instant synchronization update between different database tables, trigger transaction processing can ensure security and synchronization of data updates.
Second, implement steps
Suppose the local database server is A, the remote database server is B
1. Firstly, the MSDTC service of the local database server and the remote database server can be opened, and it can be opened manually or through the program, as follows:
--------------------------------------------
Remote: EXEC MASTER..XP_CMDSHEC MASQL / S "A" / u "sa" / p "" / q "EXEC MASTER..XP_CMDSHELL '' NET START
Local: EXEC MASTER..XP_CMDSHELL 'NET START MSDTC', NO_OUTPUT
--------------------------------------------
2, establish the ODBC connection of the remote database in A, as follows:
--------------------------------------------
Use master
Go
EXEC SP_ADDLINKEDSERVER
'Svrtest', --svrtest is the link server name
'',
'Msdasql',
NULL,
NULL,
'Driver = {SQL Server}; server = b; uid = sa; pwd =;' --UID is a user account that can access the target database
Go
--------------------------------------------
EXEC SP_SERVEROPTION 'SVRTEST', 'RPC', 'True' - Enables RPC
EXEC SP_SERVEROPTION 'SVRTEST', 'RPC OUT', 'TRUE' - Enables RPC
--------------------------------------------
3, you can write the trigger you need to operate the remote database table, there are three ways
1, OpenQuery (LINKED Server, 'Query') - Linked Server is the SVRTEST, Query, which is set above, query is specific SQL operation statement
2, OpenRowSet ('provider_name', {'DataSource'; 'User_ID'; 'PASSWORD' | 'Provider_String'}, {[Catalog.] [Schema.] Object | 'Query'})
Specific example: delete from OpenRowSet ('sqloledb', 'xz'; 'sa'; '', test.dbo.test) Where id in (Select ID from deleted)
3, if it is a stored procedure for the remote database, the link server name can be directly called directly, the database name .dbo. Stored procedure name, this method can also be used directly to the table call.
Third, instance demo
The following is taken as an example of a (remote data source) and B (local data source) machine, the insertion trigger of the Web_Reginfo table of the Database2 database is realized to instantly synchronize the Database1 Corinfo Table and ProductReg Table: 1, confirm the opening Whether the MDDTC of A and B is open.
2, ODBC connections in B open A:
3. Web_regInfo table written in b on b, insert the trigger, as follows:
4, insert database testing and display test results
Fourth, other supplementary description
