Use DTS to synchronize or migrate triggers from the source database - Data Transmission Service

If a trigger in the source database updates a table to be synchronized or migrated, data inconsistency may occur between the source and destination databases because the trigger is synchronized or migrated to the destination database at an early stage. This topic describes how to synchronize or migrate triggers from the source database to prevent data inconsistency between the source and destination databases.

Prerequisites

The source and destination databases are of supported types. For example, the data is synchronized or migrated from an ApsaraDB RDS for MariaDB instance to an ApsaraDB RDS for MySQL instance. For more information, see the Supported database types section of this topic.
Schema synchronization or migration and incremental data synchronization or migration are selected as synchronization or migration types when you configure the Data Transmission Service (DTS) instance.

Supported database types

Source database type	Destination database type	Description
MySQL, PolarDB for MySQL, and MariaDB	MySQL, PolarDB for MySQL, and ApsaraDB RDS for MariaDB	If you set the Method to Migrate Triggers in Source Database parameter to Automatically Migrate, DTS adds a trigger control statement to each synchronized or migrated trigger in the destination database. By default, DTS does not execute these triggers in the destination database. For more information, see the Automatically synchronize or migrate triggers section of this topic.
SQL Server	SQL Server	If you set the Method to Migrate Triggers in Source Database parameter to Automatically Migrate, DTS automatically migrates triggers to the destination database. Important If incremental data changes in the source database cause triggers in both the source and destination databases to update data, dirty data may be generated in the destination database. We recommend that you set the Method to Migrate Triggers in Source Database parameter to Manual Migration and perform subsequent operations.

Configure the DTS instance

In the Configure Objects and Advanced Settings step, you can configure the Method to Migrate Triggers in Source Database parameter.

If you set this parameter to Manual Migration, DTS generates the object ignored due to incremental migration is enabled alert when a trigger is encountered during schema synchronization or migration. DTS does not synchronize or migrate the trigger to the destination database. You must manually synchronize or migrate triggers from the source database to the destination database before incremental data synchronization or migration is complete. For more information, see the Manually synchronize or migrate triggers section of this topic.
If you set this parameter to Automatically Migrate, no additional operations are required.

Manually synchronize or migrate triggers

Note

The procedure for manually migrating triggers from the source database is the same as the procedure for manually synchronizing triggers from the source database. This example shows how to manually synchronize triggers from the source database.

Go to the Data Synchronization Tasks page.
1. Log on to the Data Management (DMS) console.
2. In the top navigation bar, click DTS.
3. In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.
Note
- Operations may vary based on the mode and layout of the DMS console. For more information, see Simple mode and Customize the layout and style of the DMS console.
- You can also go to the Data Synchronization Tasks page of the new DTS console.
From the drop-down list to the right of Data Synchronization Tasks, select the region in which the data synchronization instance resides.
Note If you use the new DTS console, you must select the region in which the data synchronization instance resides in the top navigation bar.
Find the DTS instance that you want to manage and click the instance ID.
Optional. In the left-side pane, click Task Management.
In the Progress section, click the Incremental Write module.
On the Basic Information tab, click Migrate Triggers.
Note
If Migrate Triggers is not displayed, no trigger is encountered during schema synchronization or migration.
You can view the result of trigger synchronization in the Schema Migration3 module of the Progress section.

Automatically synchronize or migrate triggers

DTS copies the code of triggers from the source database, adds a trigger control statement to the headers of triggers, and then writes the code to the destination database. The following sample code provides examples on how to create a trigger:

Create a trigger in the source database:

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW
  BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
  END;

Create a trigger in the destination database:

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW
  BEGIN
    IF (SELECT @`__#aliyun_dts_writer#__) IS NULL THEN
        INSERT INTO test2 SET a2 = NEW.a1;
    END IF;
  END;

FAQ

How do I select schema synchronization or migration and incremental data synchronization or migration when I configure a DTS instance?
When you configure a data synchronization instance, select Schema Synchronization and Incremental Data Synchronization for the Synchronization Types parameter. When you configure a data migration instance, select Schema Migration and Incremental Data Migration for the Migration Types parameter.
How do I complete incremental data synchronization or migration?
You can terminate, reset, or release a DTS instance to complete incremental data synchronization or migration. For more information, see Terminate a DTS instance, Reset a DTS instance, and Release DTS instances.