[Chapter 3. Detailed Configuration [section B],

Source: Internet
Author: User
Tags table definition

[Chapter 3. Detailed Configuration [section B],
3.4 Table Triggers

Mongoricds uses a database trigger to capture the data to be synchronized. The TRIGGER of metrics ricds is defined in the TRIGGER table. SymmetricDS uses a row of records to generate a database trigger. Only when a trigger specifies a valid Route will the trigger be automatically generated by metrics ricds. The source_node_group_id of the ROUTE must correspond to the group id of the current node.

The source_table_name column of the TRIGGER table can contain wildcards ("*"). Therefore, a row in the TRIGGER table can define synchronization operations for multiple tables. System tables and any tables starting with the prefix of the specified ricds table will be excluded. You can also provide a list of wildcard characters. If there are multiple wildcard characters, you can use a comma to determine the boundary. A wildcard can be expressed as an exclamation point ("!") To indicate a match to be excluded. The symbols are always matched from left to right. If a table already has a trigger, the table will not be matched with the wildcard (the clearly defined trigger has a higher priority than the trigger created by the wildcard ).

When determining whether a data change occurs, the trigger records data changes by default, even if the data is updated with the same value. For example, if a column in a row is updated with the value of this column, a data change will be captured. There are some global attributes, trigger. update. capture. changed. data. only. enabled (the default value is false), which allows you to reload this behavior. When it is set to true, ricds only captures the actually changed data (when the new data of a column is not equal to the old data ).

Important

Attribute trigger. update. capture. changed. data. only. enabled is currently only supported in MySQL, DB2, SQL Server, and Oracle dialects.

The following SQL statement defines a trigger that captures the data of the table item, whether inserted, updated, or deleted. The Trigger is assigned to a channel named "item.

insert into SYM_TRIGGER (trigger_id, source_table_name,          channel_id, last_update_time, create_time)                 values ('item', 'item', 'item', current_timestamp, current_timestamp); 

Important

Note that many databases allow defining multiple triggers of the same type. The trigger sequence of each database-defined trigger is different. If you have other triggers besides the triggers installed on your table, please consult your database documentation to determine whether there is a problem with this sequence.

3.4.1 Linking Triggers

The TRIGGER_ROUTER table is used to define the combination of the Trigger and Router you want to configure. The relationship between a Trigger and a Router is many-to-many. Therefore, this table is used as a connection table to define valid Trigger-Router combinations and configuration information at the Trigger-Router level.

Three important controls can be configured to a specific Trigger-Router combination: Enabled, Initial Loads, and Ping Back. The definitions of these parameters can be found in the TRIGGER_ROUTER table.

3.4.1.1 Enable/Disable Trigger Router

Each Trigger-Router combination can be enabled or disabled as needed. It is enabled by default. However, if you want to define the priority of the Trigger Router combination to be activated for some reason, you can set the enabled ID to 0. This will cause trigger-router to be sent to all nodes, but trigger-router will not be considered activated or enabled to capture data changes or route data.

3.4.1.2. Enabling "Ping Back"

By default, cyclricds avoids changing cyclic data. When a trigger is triggered, metrics ricds records the id of the origin node of the data change to the source_node_id column. In the process of route, if route causes data to be sent back to the origin node of the Data Change, the data will not be route by default. If you want the data to be returned to the origin node of the Data Change by route, You can reset the ping_back_enabled column value for the specific trigger-router combination. This enables the router to send data back to the source node of the Data Change.

3.4.2. Large Objects

The TRIGGER table also has two Lob-type settings:

Use_stream_lobs

Specify whether to capture lob type data or access the Lob type columns in the source table by calling back when data is extracted. If the value is 1, the system reads the source data through callback. If the value is 0, the trigger directly captures the data of the Lob type.

Use_capture_lobs

It implies to javasricds whether the trigger captures data of the Lob type. If set to 1, all efforts will be made to use Lob-related tools to extract and store data in the database when the trigger captures data and selects data for the initial load.

3.4.3. External Select

 

Occasionally, when a trigger is triggered, you may find that part of the data you need to capture and save appears on another table. During Route data, the data is usually used to determine the location where the data is to be route. The Trigger table definition contains an optional external_select column, which is used to specify the captured data. Once the DATA is captured, the captured DATA can be obtained in the external_data field of the DATA table during the Route period. In this case, an SQL Select statement is used. This Select statement returns the data entries that you need to route in external_select. Section3.6.7 "Utilizing External Select when Routing" has an example of using externalselect.

3.4.4. Dead Trigger

Sometimes, you may need to add additional triggers when deciding what data to initially load. These triggers are the Dead triggers, which need to be configured but do not capture any data changes. A "dead" trigger is a trigger that does not capture data changes. That is to say, the sync_on_insert, sync_on_update, and sync_on_delete attributes of a trigger are both set to false. It will be included in the initial load of the data to the target node.

Why do you need a Dead Trigger? For example, a Dead Trigger can be loaded with a read-only lookup table or used to load a table that needs to be filled by sample data or default data. Another use is to restore the data load of a one-way synchronization table. For example, the transaction information recorded by a distribution store is synchronized to the central database in one way. If the distribution store needs to recover all transaction information from the central database, the transaction information can be sent from the central database to the distribution store as part of the initial load created by the Dead Trigger in that direction.

The following SQL statement creates a non-synchronous dead Trigger. During the initial load, the Trigger sends the transaction information table of the "corp" node group to the "store" node group.

insert into sym_trigger (TRIGGER_ID,SOURCE_CATALOG_NAME,                         SOURCE_SCHEMA_NAME,SOURCE_TABLE_NAME,CHANNEL_ID,                         SYNC_ON_UPDATE,SYNC_ON_INSERT,SYNC_ON_DELETE,                         SYNC_ON_INCOMING_BATCH,NAME_FOR_UPDATE_TRIGGER,                         NAME_FOR_INSERT_TRIGGER,NAME_FOR_DELETE_TRIGGER,                         SYNC_ON_UPDATE_CONDITION,SYNC_ON_INSERT_CONDITION,                         SYNC_ON_DELETE_CONDITION,EXTERNAL_SELECT,                         TX_ID_EXPRESSION,EXCLUDED_COLUMN_NAMES,                         CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME)               values ('SALE_TRANSACTION_DEAD',null,null, 'SALE_TRANSACTION','transaction',                         0,0,0,0,null,null,null,null,null,null,null,null,null,                         current_timestamp,'demo',current_timestamp);                           insert into sym_router (ROUTER_ID,TARGET_CATALOG_NAME,TARGET_SCHEMA_NAME,                         TARGET_TABLE_NAME,SOURCE_NODE_GROUP_ID,TARGET_NODE_GROUP_ID,ROUTER_TYPE,                         ROUTER_EXPRESSION,SYNC_ON_UPDATE,SYNC_ON_INSERT,SYNC_ON_DELETE,                         CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME)               values ('CORP_2_STORE',null,null,null, 'corp','store',null,null,1,1,1,                         current_timestamp,'demo',current_timestamp);                           insert into sym_trigger_router (TRIGGER_ID,ROUTER_ID,INITIAL_LOAD_ORDER,                         INITIAL_LOAD_SELECT,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME)               values ('SALE_TRANSACTION_DEAD','CORP_2_REGION',100,null,                         current_timestamp,'demo',current_timestamp); 

3.4.5. Changing Triggers

The information of a Trigger can be updated using SQL statements to change the synchronization performance. SymmetricDS searches for changes to the Trigger table every night. It can also run the Sync Trigger Job at any time for updates. For example, to put the price_changes table into the price channel, you can use the following statement to complete the process:

update SYM_TRIGGER          set channel_id = 'price',            last_update_by = 'jsmith',            last_update_time = current_timestamp          where source_table_name = 'price_changes';

All changed configurations should be centrally managed on the registration node. At startup, the changed configuration will be synchronized to all client nodes. When the Trigger changes to the client node, the Sync Trigger Job runs automatically.

The Trigger changes will take effect only after the Sync Trigger Job is run. If you do not want to wait for the Sync Trigger Job to run at night, you can also use JMX to call the syncTrigger () method or simply restart the javasricds service. A complete Trigger change record is stored in the TRIGGER_HIST table, which is discussed in Section 4.3.5 "Sync Triggers Job.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.