Capture Mssqlservice data After modifying a table, store it uniformly in a specific table, and then synchronize the data of two libraries by code

According to some previous ideas, if there is a A, a two database, if a user through the interface generated by the update or insert modification, the operation of the data synchronized update to the B library, if the delay is allowed for 2 minutes or less

Idea one: By creating a trigger to store the changed data and the corresponding table name uniformly to a particular table, and then through the program get converted to SQL Update B database table, this article mainly explains the use of this method

Idea two: Through CDC record the content of the change, in-Park search: SQL Server change data Capture (CDC) Monitoring table data can be.

Here are the tests:

Drop  Database  t_mytest  create  database  t_mytest  --Creating a modify table use T_mytest create   table Mytest  (   ID  int NULL,   name  nvarchar () null  ,   owndstore  int null     )-- Create record modifies that database, record of that table create table Testdatabaselog  (   ID  int identity not  NULL,    state int NULL,--synchronization state 0 indicates that the initial data is not synchronized, 1 indicates that the file data required to be inserted is synchronized      changetablename nvarchar null, which database table was updated   Createdatetim e datetime  NULL,    actiontype int  null,  --1 Insert 2 Update  3 delete    actionname  nvarchar (20) NULL,   content  nvarchar (max) null  ,  --The updated contents   fromstore  int null  ,  --  The data comes from that store database  identifier   tostore  int null  ,--synchronized to that store for processing  

　　

Two create important trigger statements that can be applied to all the tables that need to be synchronized, simply by adding the trigger at the time of creation.

You do not need to modify the contents, only the trigger name and the table should be

CREATE TRIGGER [dbo].[Itrg_triggertest] On[dbo]. [Mytest After insert, UPDATE, Deleteasset nocount ON-Query the trigger-triggered table--select @ @PROCID, object_name (@ @PROCID) declare @tableNa Me nvarchar (25);--declaring variable DECLARE @D bit = 0 DECLARE @i bit = 0 DECLARE @content nvarchar (max);d eclare @actiontype int; Declare @actionName nvarchar, if EXISTS (select top 1 1 from DELETED) begin SET @D = 1 End IF EXISTS (select top 1 1 FR  OM INSERTED) Beginset @i = 1 end IF @i = 1 and @D = 0 Begin Set @actiontype = 1; Set @actionName = ' inserts '--select N ' insert ' + ' insert INTO ' Select @content = (SELECT * from inserted for XML path) + '--se  Lect * from inserted for XML path (@tableName), root (' insert ') end else IF @i = 1 and @D = 1 begin set @actiontype = 2; Set @actionName = ' Update '; Select @content = (SELECT * from inserted for XML path) + ';  End ELSE BEGIN set @actiontype = 3; Set @actionName = ' delete '--select N ' delete ' + ' insert INTO ' Select @content = (SELECT * from deleted for XML path) + "; End If (@content is not null) begin SELECT @tableName =object_schema_name (parent_id) + '. ' + object_name (parent_id) from sys.triggers WHERE object_id = @ @PROCIDinsert into TESTD Atabaselog (Changetablename, Createdatetime, ActionType, ActionName, Content,fromstore) VALUES ( @tableName, GETDATE (), @actiontype, @actionName, @content, 1); End GO

　　

--Test data  insert into Mytest values (1, ' Apple ', 1)  insert into Mytest values (2, ' Sydney ', 1)  insert into Mytest values (3 , ' banana ', 1)

　　

Get results tested

Capture Mssqlservice data After modifying a table, store it uniformly in a specific table, and then synchronize the data of two libraries by code