Symmetricds document Translation--"Chapter 3. Detailed configuration (config) [section B] "

3.4 Table Triggers

SYMMETRICDS uses database triggers to capture the data to be synchronized. The SYMMETRICDS trigger is defined in the trigger table. SYMMETRICDS generates a database trigger using a single line of records inside. This trigger is automatically generated by SYMMETRICDS only when a trigger specifies a valid route, and 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 wildcard characters ("*"), so a row in the trigger table can define synchronization operations on multiple tables. system tables and any tables starting with the Symmetricds table prefix will be excluded. You can also provide a list of wildcard symbols. If you have more than one wildcard symbol, you can use a comma to determine the bounds. A wildcard symbol can be an exclamation point ("!") ) begins to indicate a match to exclude. Symbols are always matched from left to right. If a table already has a trigger, that table will not be in wildcard matching (the explicitly defined trigger has a higher precedence than the one created by the wildcard character).

When determining whether a data change occurs, by default, the trigger records data changes, even if the data is updated once with the same value. For example, if one column in a row is updated with the value of the column, a data change will be captured. There are some global properties, trigger.update.capture.changed.data.only.enabled (the default is False), which allows you to overload this behavior. When set to True, Symmetricds will only capture truly changed data (when the new data for a column is not equal to the old data).

Important

The attribute trigger.update.capture.changed.data.only.enabled is currently supported only in Mysql,db2,sql server and Oracle dialects.

The following SQL statement defines a trigger that captures the data for the table item, whether it is an insert, an update, or a delete. The trigger is assigned to a channel called "Item".

Insert into Sym_trigger (trigger_id, Source_table_name,          channel_id, Last_update_time, Create_time)                 

Important

Note that many databases allow multiple triggers of the same type to be defined. The trigger sequence for each database definition trigger is different. If you have other triggers besides Symmetricds installed on your table, check with your database documentation to determine if there is a problem with the order.

3.4.1 Linking Triggers

The Trigger_router table is used to define the combination of TRIGGER and router that you want to configure. The relationship between trigger and router is many-to-many, so this table is used as a join table to define valid Trigger-router combinations and configuration information at the Trigger-router level.

There are three important controls that 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 turned on or off when needed. The default is on, but if for some reason you want to define the priority of the trigger router combination being activated, you can set the enabled ID to 0. This causes the Trigger-router map to be sent to all nodes, but the Trigger-router map will not be considered activated or turned on to capture data changes or route data.

3.4.1.2. Enabling "Ping back"

By default, Symmetricds avoids changes to the cyclic data. When a trigger is triggered, SYMMETRICDS records the ID of the originating node of the data change into the source_node_id column. In the course of the route, if the route causes the data to be sent back to the originating node of the data change, by default, the data is not routed to the route. If you want the data to be able to be route back to the originating node of the data change, you can reset the value of the ping_back_enabled column for a particular trigger-router combination. This will enable router to have the ability to send data back to the originating node for data changes.

3.4.2. Large Objects

There are also two LOB type related settings in the trigger table:

Use_stream_lobs

Specifies whether to capture data from the LOB type when the trigger is triggered, or to access a column of the LOB type in the source table in the same way that the call flow is fetched when the data is extracted. A value of 1 indicates that the source data is read through a callback, and a value of 0 o'clock causes the trigger to capture data for the LOB type directly.

Use_capture_lobs

Give Symmetricds a hint whether the trigger captures data of the LOB type. If set to 1, every effort is made to extract and store data from the database using LOB-related tools 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 some of the data you need to capture and save appears on another table. Route data, this data is often used to determine where the data is to be taken by the route. The trigger table definition contains an optional external_select column that is used to specify the data being captured. Once the data is captured, the captured data can be obtained during the route in the External_data field of the data table. In this case, using a SQL SELECT statement, this SELECT statement will return the data entry for the route you need 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 load initially. These triggers, which are dead Trigger, need to be configured but do not capture any data changes. A "dead" trigger is a trigger that does not capture data changes. In other words, the sync_on_insert,sync_on_update and Sync_on_delete properties of a trigger are 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 read-only lookup tables (lookup table), or it can be used to load a table that needs to be populated with sample data or default data. Another use is to restore the data payload of a one-way synchronized table. For example, a distribution store records transaction information that is synchronized to the central database in one Direction. If the distribution store needs to recover all the transaction information from the central database, these transaction information can be sent from the central database to the distribution store as part of the initial load established in the dead trigger in that direction.

The following SQL statement establishes an unsynchronized dead Trigger, which, during the initial load, sends data from 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,chan nel_id, Sync_on_update,sync_on_insert,sync_on_delete, Sync_on_incoming_bat                         Ch,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_UPDA                         Te_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_ins Ert,sync_on_delete, Create_time,last_update_by,last_update_time) VALUES (' Corp_2_sto                           RE ', 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_RE  GION ', 100,null, Current_timestamp, ' demo ', Current_timestamp);

3.4.5. Changing Triggers

A trigger information can be updated using SQL statements to change the performance of the synchronization. Symmetricds will look for changes to the Trigger table every night, or you can run the Sync Trigger job at any time to update it. For example, putting the table price_changes in the price channel can be done using the following statement:

Update Sym_trigger          Set channel_id = ' Price ',            last_update_by = ' jsmith ',            last_update_time = Current_ Timestamp          where source_table_name = ' price_changes ';

All configuration changes should be centrally managed in the registry node. At startup, the changed configuration is synchronized to all client nodes. When the Trigger changes arrive at the client node, the Sync Trigger job will run automatically.

Changes to the trigger will not take effect until the Sync Trigger job runs. Don't want to wait for the sync Trigger job to run at night, you can also call the Synctrigger () method via JMX or simply restart the Symmetricds service. A complete record of trigger changes is stored in table trigger_hist, which is discussed in section 4.3.5 "Sync Triggers Job".

Symmetricds document Translation--"Chapter 3. Detailed configuration (config) [section B] "