A kind of table level bi-directional synchronization method and system based on trigger[technical field]
The present invention relates to data synchronization technology fields, and in particular to a kind of table level bi-directional synchronization realization side based on triggerMethod and system.
[background technique]
With deepening continuously for data exchange and data sharing application, user be not content with unidirectional data integration orPerson is synchronous, and two-way feature is increasingly presented between the different departments or between the superior and the subordinate flowing of data.Such as in dataIn the application of heart class, generally require from each department's synchrodata to consolidated storage, but with the development that department applies, a departmentWhile providing data to center, it is also possible to which the data for needing to obtain other departments from center are used for the application of this department and divideAnalysis, which results in the demands of bi-directional synchronization.
Table level bi-directional data is synchronous to be realized generally by the one-way synchronization for combining two table levels, currently used table levelOne-way synchronization mode has md5, timestamp, log analysis mode and trigger mode, and the difference of these types of mode essentially consists inThe mode for obtaining delta data is different, is described as follows:
MD5 mode: this, which is achieved in that, creates a MD5 table for comparing according to base table in the database.In MD5 tableContain the major key or unique constraint column in base table, and the MD5 generated by the value that other in base table arrange by MD5 functionColumn.The data for obtaining variation when every subsynchronous by comparing base table and MD5 table, since base table and MD5 table are having the same onlyOne column are positioned according to unique column, if certain data line in base table, without in MD5 table, then the row data are that a line is newThe data of insertion；Otherwise if certain data line not in base table, and in MD5 table, then the row data are the deleted number of a lineAccording to；If certain data line is both in base table, also in MD5 table, but MD5 value is different, then the row data are the number that a line was modifiedAccording to.This method needs to be traversed for base table and MD5 table since calculation amount is larger, so being not suitable for the biggish table of data volume.
Timestamp mode: timestamp mode is to create a timestamp table for comparing according to base table in the database.The major key or unique constraint column in base table, and the timestamp column specified in base table are contained in timestamp table.It obtains and becomesThe mode for changing data is consistent with MD5 base table, only obtains the comparison of timestamp value when modifying data instead of the ratio of MD5 valueCompared with being suitable for the table of the stamp column of having time in base table.Timestamp mode is only used for the table comprising timestamp column in base table, although savingWhen having omited the calculating of md5, but having compared there is still a need for traversal base tables and timestamp table, therefore are also not and are suitble to data volume bigTable is of limited application.
Log analysis mode: log analysis mode is to obtain data by parsing the online or archive log of databaseIncreasing, delete, changeization.Which performance is very good, but needs to dispose log parsing agency on database server, in additionDue to recorded in database journal be all tables in entire database variation, which synchronizing process is usually continuousIn real time, the occasion synchronous and high to requirement of real-time suitable for library grade, such as active and standby library are synchronous, system dual-active, if usedAdditional configurating filtered condition is needed in the periodic synchronization of table level, using inconvenience.
Trigger mode: by establishing trigger on base table, the additions and deletions occurred on base table is captured in trigger and change behaviourMake, to increasing on acquisition table, delete, change delta data.Trigger mode is a kind of more common mode, obtains incremental dataWhen, better performances less to the consumption of system resource, there is no limit at present using more for application range.
However, all being had the following problems when above-mentioned several methods of synchronization are used for bi-directional synchronization:
First is that circulation synchronous collision problem, the data that the data variation of synchronization system triggering can not be triggered with ordinary user becomeChange distinguishes, and the data for being synchronized to base table B from base table A can be taken as delta data and be resynchronized in base table A, equally, fromThe data that base table B is synchronized to base table A can be taken as delta data and be resynchronized in base table B, so that it is dead to cause data to synchronizeCirculation；When there are three or more base tables, if being both needed to the bi-directional synchronization of progress data between every two base table, every twoThe synchronous endless loop of data can be all formed between a base table, coverage is wider.Second is that data modification collision problem, i.e. base table A,Base table B has modified the same field of the identical record of major key simultaneously, at this time if base table A to base table B and base table B to base table ABi-directional synchronization be performed simultaneously, then the record of the base table A and base table B that are modified only exchanges position, and the data on both sides are simultaneously differentIt causes.Third is that data are inserted into collision problem, i.e. base table A, base table B insert the identical data of major key, execute bi-directional synchronization number at this timeIt can be reported according to library and violate unique constraints mistake, lead to synchronization failure.
In consideration of it, overcoming defect present in the above-mentioned prior art is the art urgent problem to be solved.
[summary of the invention]
The technical problem to be solved in the invention is:
When carrying out the bi-directional synchronization of table level data in traditional scheme, synchronization system and other ordinary user's bring base table numbersIt cannot be distinguished according to variation, the data for being synchronized to base table B from base table A can be taken as delta data and be resynchronized in base table A, thusThe endless loop for causing data synchronous；Meanwhile two base tables can also have data variation collision problem, influence synchronizing for dataJourney.
The present invention reaches above-mentioned purpose by following technical solution:
In a first aspect, the present invention provides a kind of table level bi-directional synchronization implementation method based on trigger, comprising:
Capture the source data variation in current base table；Wherein, the source data variation is by synchronization system and/or ordinary userTriggering；
The source data variation by private subscribers triggering is filtered out, and the source data triggered by non-dedicated user is changed and is writtenInto corresponding CDC table；Wherein, the private subscribers are created corresponding to the synchronization system, and the non-dedicated user is except instituteState the ordinary user outside synchronization system；
Synchronization system reads the record in the CDC table, and according to the data of record to participate in bi-directional synchronization anotherIt is synchronous that base table carries out data.
Preferably, the source data variation in the current base table of capture specifically: using in trigger A capture base table ASource data variation, the source data using trigger B capture base table B change；
The source data variation filtered out by private subscribers triggering, and the source data triggered by non-dedicated user is changedBe written in corresponding CDC table, specifically: trigger A by identification triggering base table A source data variation user, filter out byThe source data variation of private subscribers triggering, and the source data variation that non-dedicated user triggers is written in A_CDC table；TriggerThe user that B is changed by identifying triggering base table B source data filters out the source data variation by private subscribers triggering, and will be non-specialThe source data variation triggered with user is written in B_CDC table；Wherein, base table A and base table B is respectively participate in bi-directional synchronization twoA base table.
Preferably, the synchronization system reads the record in the CDC table, and two-way same to participating according to the data of recordAnother base table of step carries out data and synchronizes, specifically:
Synchronization system reads the record of the data variation in the A_CDC table and the B_CDC table；
According to the CDC_ID column and CDC_OPT column in the A_CDC table, corresponding data operation is executed on base table B, is completedIt is synchronous by the data of the base table A to the base table B；
According to the CDC_ID column and CDC_OPT column in the B_CDC table, corresponding data operation is executed on base table A, is completedIt is synchronous by the data of the base table B to the base table A.
Preferably, the CDC_ID column and CDC_OPT according in the A_CDC table arrange, and execute respective counts on base table BAccording to operation, completes to be synchronized by the data of the base table A to the base table B, specifically include:
Compare the record in the A_CDC table and the B_CDC table, judges whether there is data collision in two CDC tables；
If without data collision, according to the CDC_ID column and CDC_OPT column in the A_CDC table, in the base table BUpper to execute corresponding data manipulation, the data for completing the base table A to the base table B are synchronous；
It if there is data collision, is then handled according to the priority of data in two CDC tables, completes the base table AData to the base table B are synchronous.
Preferably, the priority according to data in two CDC tables is handled, and completes the base table A to the baseThe data of table B are synchronous, specifically:
Judge the data priority in the A_CDC table and the B_CDC table；
If the data priority in the A_CDC table is higher than the data priority in the B_CDC table, ignore punchingIt is prominent, colliding data is synchronized in the base table B；
If the data priority in the A_CDC table abandons conflict lower than the data priority in the B_CDC tableThe data not conflicted are synchronized in the base table B by data.
Preferably, the data collision includes data modification conflict and data insertion conflict；Wherein, the data modification punchingIt is prominent specifically: to have modified the same field of the identical record of major key in the base table A and base table B simultaneously；The dataInsertion conflict specifically: the identical data of major key are inserted in the base table A and base table B.
Preferably, before the data for carrying out the base table A to the base table B are synchronous, judge presently described base table A to instituteState base table B data it is synchronous whether headed by it is subsynchronous, if not synchronizing for the first time, then according to upper subsynchronous corresponding CDC_ID,Remove data synchronized in the A_CDC table；After the data for completing the base table A to the base table B are synchronous, recordCurrent corresponding CDC_ID value in lower A_CDC table.
Preferably, before carrying out data and synchronizing, the database for participating in bi-directional synchronization need to be initialized, for eachDatabase, the initialization procedure specifically include:
Private subscribers are created for the synchronization system on the database, and authorize corresponding permission；Wherein, the dedicated useFamily correspondence carries dedicated user identifier；
Increase CDC_ID and CDC_OPT two on the basis of base table to arrange, the creation of CDC table is completed, for recording by describedThe base table source data variation of non-dedicated user's triggering；
Sequence is created, to give the CDC_ID assignment；
Trigger is created, for capturing the source data occurred on the base table variation, and filters out and is triggered by private subscribersSource data variation, the source data triggered by non-dedicated user variation is written in corresponding CDC table.
Preferably, the CDC_ID in the CDC table is classified as progressive whole number value, indicates the sequence of operation；The CDC_OPTCharacter types are classified as, indicate the type of operation；Wherein, the type of the operation includes insertion, updates and delete.
Second aspect, the present invention also provides a kind of, and the table level bi-directional synchronization based on trigger realizes system, for realizingThe implementation method of the table level bi-directional synchronization of above-mentioned first aspect, including base table A, A_CDC table, trigger A, base table B, B_CDC table,Trigger B and synchronization system, wherein for the synchronization system create private subscribers, and the private subscribers be corresponding with it is dedicatedUser identifier；
The base table A and base table B be participate in bi-directional synchronization two base tables, be respectively used to storage source data, andData variation occurs when private subscribers or non-dedicated user's operation；
The trigger A and trigger B is respectively used to capture the source data variation of the base table A and base table B,And filter out and changed by the source data of private subscribers triggering, the source data variation that non-dedicated user triggers is respectively written into A_In CDC table and B_CDC table；
The synchronization system is used for the record according to A_CDC table, and it is synchronous that data are completed on base table B；And according to B_CDCIt is synchronous to complete data on base table A for the record of table.
The beneficial effects of the present invention are:
In a kind of table level bi-directional synchronization implementation method based on trigger provided by the invention, created for synchronization system dedicatedUser so that trigger is when capturing the data variation in base table, can according to the user that current trigger data changes come pairRecord is filtered, to prevent solving the circulation of bi-directional synchronization in the data variation write-in CDC table triggered by synchronization systemTrigger Problems smoothly complete the function of bi-directional synchronization eventually by two one-way synchronizations of combination.Meanwhile passing through data priority machineSystem retains the high data of priority, ignores priority when data modification or insertion conflict occur in two synchronous base tablesLow data, to solve the problems, such as incidental data collision in bi-directional synchronization.
[Detailed description of the invention]
In order to illustrate the technical solution of the embodiments of the present invention more clearly, will make below to required in the embodiment of the present inventionAttached drawing is briefly described.It should be evident that drawings described below is only some embodiments of the present invention, forFor those of ordinary skill in the art, without creative efforts, it can also be obtained according to these attached drawings otherAttached drawing.
Fig. 1 is a kind of flow chart of the table level bi-directional synchronization implementation method based on trigger provided in an embodiment of the present invention；
Fig. 2 is the specific implementation flow chart of step 203 in Fig. 1；
Fig. 3 is any one-way synchronization process in a kind of table level bi-directional synchronization based on trigger provided in an embodiment of the present inventionFlow chart；
Fig. 4 is the database initialization procedure before data provided in an embodiment of the present invention are synchronous；
Fig. 5 is the composition figure that a kind of table level bi-directional synchronization based on trigger provided in an embodiment of the present invention realizes system.
[specific embodiment]
In order to make the objectives, technical solutions, and advantages of the present invention clearer, with reference to the accompanying drawings and embodiments, rightThe present invention is further elaborated.It should be appreciated that the specific embodiments described herein are merely illustrative of the present invention, andIt is not used in the restriction present invention.
In addition, as long as technical characteristic involved in the various embodiments of the present invention described below is each other notConstituting conflict can be combined with each other.Just with reference to drawings and examples, in conjunction with coming, the present invention will be described in detail below.
For ease of description, introducing different objects herein: user 1, user 2, server A, server B, A_CDC table, B_CDC table, base table A, base table B, synchronization system, trigger A, trigger B.It is synchronous in traditional data double-way based on triggerIn, user 1 and user 2 are far-end operation personnel, i.e. ordinary user；Synchronization system is for realizing the data between two serversIt is synchronous；A_CDC table and B_CDC table are respectively to be used in server A and server B for synchronization system periodic reading and completed sameThe data object of operation is walked, CDC table, that is, delta data captures (Change DataCapture, be abbreviated as CDC) table；Base table A andBase table B is two base tables for participating in bi-directional synchronization, is located at server A side and server B side, and be respectively used to storeThe corresponding source data A of the server A and corresponding source data B of server B；Trigger A and trigger B is respectively used to capture base table AWith the source data variation in base table B.For server A, it is assumed that user 1 modifies to source data A, then trigger A is caughtThe data variation of source data A in base table A is received, and changing content is written in corresponding A_CDC table；At this point, synchronization systemPeriodic reading A_CDC table is simultaneously synchronized to server B, modifies the source data B in base table B and realizes that data are synchronous.Source data B is repairedAfter changing, trigger B will correspondingly capture the data variation of source data B in base table B, further be written to modification content certainlyIn the corresponding B_CDC table of body, at this point, synchronization system reads B_CDC table and is synchronized to server A, and so on, it will result in numberAccording to synchronous endless loop.
Embodiment 1:
The table level bi-directional synchronization implementation method based on trigger that the present invention provides a kind of still uses base table A and base hereinTable B is indicated to participate in two base tables of bi-directional synchronization, is respectively used to store source data A and source data B.As shown in Figure 1, the methodSpecifically includes the following steps:
Step 1, the source data variation in current base table is captured；Wherein, source data variation is by synchronization system and/or generalGeneral family triggering.In embodiments of the present invention, the variation of source data is still captured using trigger: base is captured using trigger ASource data variation in Table A, the source data using trigger B capture base table B change.For the source data A in base table A,Data variation may be the operation from ordinary user, for example above-mentioned user 1 or user 2 repair the source data A data carried outChange；It is also possible that carrying out the operation of self-synchronizing system, i.e., by the synchronous bring data of the data of the base table B to the base table AVariation；It is also similar for the source data B in base table B.When trigger captures the variation of the source data in corresponding base table, triggeringDevice is activated.
Step 2, the source data variation by private subscribers triggering is filtered out, and the source data triggered by non-dedicated user is becomeChange is written in corresponding CDC table.Specifically: trigger A is filtered out by the user of identification triggering base table A source data variationIt is written in A_CDC table by the source data variation of private subscribers triggering, and by the source data variation that non-dedicated user triggers；TriggeringDevice B filters out the source data variation by private subscribers triggering, and will be non-by the user of identification triggering base table B source data variationThe source data variation of private subscribers triggering is written in B_CDC table.
Wherein, the private subscribers correspond to the synchronization system creation, and the non-dedicated user as removes describedOrdinary user outside synchronization system, and the private subscribers carry dedicated user identifier, the non-dedicated use in creationFamily carries common user identifier.After trigger, which captures the source data in corresponding base table, to be changed and then be activated, triggeringDevice is correspondingly received the corresponding user identifier of user of trigger data variation, thus the source data variation for triggering synchronization systemIt is different from the data variation of ordinary user's triggering.Trigger can determine whether corresponding source data by being identified to user identifierChange source, and then executes different operations to the data variation of separate sources.For example, by taking trigger A as an example, trigger A quiltThe user identifier received after activation to itself carries out identification judgement, and if it is dedicated user identifier, then confirmation is by synchronizingThe data variation of system bring source data A, that is, the data of the synchronous bring source data A of data by base table B to base table AVariation；The A_CDC table is not written into the changing content of source data A at this time, reads the A_CDC so as to avoid synchronization systemData variation content in table is synchronized to server B in turn, also it is avoided that causing synchronous endless loop.It is marked if it is common userKnow, then confirmation is by the data variation of the brings source data such as common user 1 or user 2 A, at this time by the data of source data AChanging content is written in corresponding A_CDC table, so that synchronization system reads the data variation content in the A_CDC table in turnIt is synchronized to server B.
Step 3, synchronization system reads the record in the CDC table, and according to the data of record to participation bi-directional synchronizationIt is synchronous that another base table carries out data.In this step, synchronization system periodically executes synchronizing process, table level data it is two-way sameStep process generally realized by two one-way synchronizations of combination, i.e., the one-way synchronization of the described base table A to the base table B and describedThe one-way synchronization of base table B to the base table A carries out two one-way synchronization processes successively in a synchronization job to completeBi-directional data is synchronous.With reference to Fig. 2, the step 203 specifically includes the following steps:
Step 31, synchronization system reads the record of the data variation in the A_CDC table and the B_CDC table.By describedIn step 2 after the filtration treatment of trigger, the ordinary user in addition to synchronization system is only recorded in the A_CDC table in the baseThe data variation triggered on Table A, the ordinary user only recorded in addition to synchronization system in B_CDC table trigger on the base table BData variation；
Step 32, according to the CDC_ID column and CDC_OPT column in the A_CDC table, corresponding data behaviour is executed on base table BMake, completes synchronous by the data of the base table A to the base table B；
Step 33, according to the CDC_ID column and CDC_OPT column in the B_CDC table, corresponding data behaviour is executed on base table AMake, completes synchronous by the data of the base table B to the base table A.Wherein, the step 32 and the step 33 are two unidirectionalSynchronizing process, two interchangeable sequences of step, on the bi-directional synchronization of data without influence.
Wherein, the CDC table is exactly to increase CDC_ID and CDC_OPT two on the basis of base table to arrange, the CDC_IDIt is classified as progressive whole number value, indicates the sequence of operation；The CDC_OPT is classified as character types, indicates the type of operation, the behaviourThe type of work includes insertion, updates and delete.In this way, according to CDC_ID column and CDC_OPT column in current CDC table, i.e.,It can determine the operation order and action type when carrying out data on another base table and synchronizing.For further, the CDC_OPTColumn only 4 kinds of values: ' I', ' UO', ' UN' and ' D', wherein ' I' indicate insertion, ' UO' and ' UN' indicates to update, and UO is indicatedValue before update, UN indicate updated value, ' D' expression deletion.
In a kind of table level bi-directional synchronization implementation method based on trigger provided in an embodiment of the present invention, created for synchronization systemDedicated user is built, and is corresponding with dedicated user identifier, to be different from the operation of ordinary user's triggering, so that trigger is being caughtWhen receiving the data variation in base table, record can be filtered according to the user that current trigger data changes, to preventBy solving the circulation Trigger Problems of bi-directional synchronization, eventually by combination in the data variation write-in CDC table of synchronization system triggeringTwo one-way synchronizations smoothly complete the function of bi-directional synchronization.
Embodiment 2:
In a synchronization job, it is synchronous to complete bi-directional data successively to carry out two one-way synchronization processes, and twoThe implementation procedure of one-way synchronization be it is identical, then on the basis of above-described embodiment 1, the embodiment of the present invention is arrived with the base table AFor the one-way data of the base table B is synchronous, the execution expansion of the step 32 is discussed in detail.
Meanwhile when the variation of source data occurs in the base table A and the base table B, may exist in two base tablesDelta data conflict, the delta data conflict include data modification conflict and data insertion conflict.Wherein, the data modificationConflict specifically: have modified the same field of the identical record of major key in the base table A and base table B simultaneously, at this time such asThe bi-directional synchronization of fruit base table A to base table B and base table B to base table A are performed simultaneously, then the record of the base table A and base table B that are modified are onlyIt is exchange position, the data on both sides are not consistent.The data insertion conflict specifically: inserted in the base table A and base table BThe identical data of major key are entered, at this time if executing bi-directional synchronization, database, which can be reported, violates unique constraints mistake, and then causesSynchronization failure.
In view of above-mentioned data modification conflict and data are inserted into the problem of conflicting, and step 32 described in the embodiment of the present invention is specificImplementation process is as follows:
First, it is determined that it is subsynchronous headed by the data synchronization whether of presently described base table A to the base table B, if not headIt is subsynchronous, then according to upper hyposynchronous operation serial number CDC_ID, remove data synchronized in the A_CDC table；If it isIt synchronizes for the first time, then ignores the step, execute step 322 directly down；
Secondly, the record in the A_CDC table and the B_CDC table, judges whether there is data punching in two CDC tablesIt is prominent；If, according to the CDC_ID column and CDC_OPT column in the A_CDC table, held on the base table B without data collisionThe corresponding data manipulation of row, the data for completing the base table A to the base table B are synchronous；
It if there is data collision, is then handled according to the priority of data in two CDC tables, completes the base table AData to the base table B are synchronous；Specifically: judge the data priority in the A_CDC table and the B_CDC table；IfData priority in the A_CDC table is higher than the data priority in the B_CDC table, then ignores conflict, and by number of collisionsAccording to being synchronized in the base table B；If the data priority in the A_CDC table is lower than the high priority data in the B_CDC tableGrade, then abandon colliding data, the data not conflicted be synchronized in the base table B.
Finally, recording current corresponding CDC_ID value in the A_CDC table after synchronously completing.
By above three step, the one-way data that the base table A to the base table B can be completed is synchronous, for from instituteThe one-way data synchronizing process for stating base table B to the base table A can refer to the above process, and details are not described herein again.When from the base table ATo the base table B carry out one-way data it is synchronous when, server A end is source, and server B is held for the purpose of end；When from the baseWhen table B to base table A progress one-way data is synchronous, server B end is source, and server A is held for the purpose of end, then any unidirectionalThe entire flow figure of synchronizing process is as shown in Figure 3.
In embodiments of the present invention, it by data priority mechanism, is rushed when data modification occurs in two synchronous base tablesWhen the insertion conflict of prominent or data, retains the high data of priority, ignore the low data of priority, to solve bi-directional synchronizationIn data collision problem, the bi-directional synchronization of table level data has been better achieved.
Embodiment 3:
Initialization procedure and data synchronization process are broadly divided into for the bi-directional synchronization of table level data, wherein initializedJourney only needs to be implemented once, and it is periodical execution that data, which synchronize,.It is synchronous that above-described embodiment 1 and embodiment 2 describe dataJourney, on this basis, the embodiment of the present invention specifically introduce the initialization procedure before data synchronize.
When bi-directional synchronization, two databases for participating in bi-directional synchronization require to carry out initial work, step and methodBe it is identical, then for each database, with reference to Fig. 4, the initialization procedure specifically includes the following steps:
Step 01, private subscribers are created for the synchronization system on the database, and authorizes corresponding permission；Wherein, instituteIt states private subscribers correspondence and carries dedicated user identifier.
For the user 1 and user 2 of common user, such as above-mentioned introduction, it is corresponding with common user identifier；In order toIt is different from ordinary user, creates private subscribers for synchronization system, by creating private subscribers, this is can be used specially in the synchronization systemDatabase is connected with user, and executes initialization and the additions and deletions of data change operation；And common user is non-dedicated user.ItsIn, for that may have the bi-directional synchronization of one or more base tables on a database, regardless of needing to carry out how many a basesThe bi-directional synchronization of table, all only needs to create a private subscribers, and the bi-directional synchronization process of multiple base tables can share the private subscribersUser identifier.Meanwhile corresponding different database, a private subscribers can also be only created, a user identifier is shared.ItsIn, the sql sentence for creating private subscribers is as follows:
create user USER1identified by"******"；
After creating private subscribers, authorizes private subscribers corresponding DBA permission, make the private subscribers can be with lookup table dataAnd the creation objects such as table and trigger, sql sentence are as follows:
grant DBA to USER1；
Step 02, increase CDC_ID and CDC_OPT two on the basis of base table to arrange, the creation of CDC table is completed, for recordingBy the base table source data variation of non-dedicated user's triggering；Namely ordinary user of the record in addition to synchronization system is in base tableOn operation.
Assuming that the definition statement of base table is as follows:
CREATE TABLE"TABLE1"
(
"COLUMN1"NUMBER(10,0)NOT NULL,
"COLUMN2"VARCHAR2(50),
"COLUMN3"DATETIME(6),
PRIMARY KEY("COLUMN1")
)
The CDC table is exactly to increase CDC_ID and CDC_OPT two on the basis of base table to arrange, and CDC_ID is classified as incrementalInteger value indicates the sequence of operation；CDC_OPT is classified as character types, indicates the type of operation, which only has 4 kinds of values: ' I', 'UO', ' UN' and ' D'；Wherein ' I' indicate insertion, ' UO' and ' UN' indicates to update, and UO indicates the value before updating, and UN is indicatedUpdated value, ' D' expression deletion.Then the definition statement of CDC table is as follows:
CREATE TABLE"TABLE1_TRIGGER_CDC"
(
"CDC_ID"BIGINT NOT NULL,
"CDC_OPT"VARCHAR(2)NOT NULL,
"COLUMN1"NUMBER,
"COLUMN2"VARCHAR2(50),
"COLUMN3"DATETIME(6),
PRIMARY KEY("CDC_ID","CDC_OPT"))；
Step 03, sequence is created, to give the CDC_ID assignment.
The value being increased continuously can be generated by creating sequence, and then for indicating in trigger to CDC_ID assignmentOperation order；It can be CDC_ with being added certainly instead of sequence such as ms sql server in the database for not supporting sequence objectID assignment.Wherein, the sql sentence for creating sequence is as follows:
CREATE SEQUENCE"SEQ_TABLE1_TRIGGER_CDC"；
Step 04, trigger is created, for capturing the source data occurred on the base table variation, and is filtered out by dedicated useThe source data variation of family triggering, the source data triggered by non-dedicated user variation is written in corresponding CDC table.Wherein, it triggersThe definition statement of device is as follows:
In embodiments of the present invention, it for each database of participation bi-directional synchronization, is all made of the above method and carries out initiallyChange process, details are not described herein again；By creating private subscribers for the synchronization system, is synchronized for subsequent data and be ready workMake, makes trigger that can directly filter out the source data variation by private subscribers triggering according to user identifier, and only by non-dedicated useThe source data variation of family triggering is written in CDC table.
Embodiment 4:
On the basis of embodiment 3 above-described embodiment 1-, the embodiment of the invention also provides a kind of tables based on triggerGrade bi-directional synchronization realizes that system, such as Fig. 5, the table level bi-directional synchronization realize that system includes base table A, A_CDC table, trigger A, baseTable B, B_CDC table, trigger B and synchronization system, wherein private subscribers, and the dedicated use are created for the synchronization systemFamily is corresponding with dedicated user identifier；
The base table A, the A_CDC table and the trigger A are arranged in server A side, the base table B, the B_CDC table and the trigger B are arranged in server B side；
The base table A and base table B is two base tables for participating in bi-directional synchronization, is respectively used to storage source data A and sourceData B, and data variation occurs in private subscribers and/or non-dedicated user's operation；
The trigger A and trigger B is respectively used to capture the source data variation of the base table A and base table B,And filter out and changed by the source data of private subscribers triggering, the source data variation that non-dedicated user triggers is respectively written into A_In CDC table and B_CDC table；Therefore, the A_CDC table is only used for recording the ordinary user in addition to synchronization system on base table AThe source data A of triggering changes, and the B_CDC table is only used for recording the ordinary user in addition to synchronization system and triggers on base table BSource data B variation；
The synchronization system is used for the record according to A_CDC table, and it is synchronous that data are completed on base table B；And according to B_CDCIt is synchronous to complete data on base table A for the record of table.
Compared with traditional bi-directional synchronization system, in embodiments of the present invention, the corresponding synchronization system creation has dedicatedUser；Modification of program is also all done in server A and server B side, so that for the synchronization system from the B_CDC tableModification after reading data to source data A, trigger A carries dedicated user identifier after being activated, to may filter that this portionDivide source data changing content, and is not written into corresponding A_CDC table.In traditional scheme, for ordinary user's bring dataVariation can carry user identifier in the trigger accordingly activated；For synchronization system, it can also carry synchronization system and log inThe user name used when server B, still, the user name are simultaneously not exclusive, and very maximum probability can be with other application in server BUser name bear the same name, therefore be not easy to distinguish.
In embodiments of the present invention, by creating private subscribers in advance in the database for synchronization system, make synchronization systemOther ordinary users are different from, different user identifiers is corresponding with, then can be carried when trigger is activated by different usersDifferent user identifiers, by identifying the corresponding user identifier of user of current trigger data variation, trigger can become dataChange record to be filtered, to prevent in the data variation write-in CDC table triggered by synchronization system, and then solves bi-directional synchronizationCirculation Trigger Problems.
The foregoing is merely illustrative of the preferred embodiments of the present invention, is not intended to limit the invention, all in essence of the inventionMade any modifications, equivalent replacements, and improvements etc., should all be included in the protection scope of the present invention within mind and principle.