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 a stage earlier than expected. This topic describes how to synchronize or migrate triggers to prevent data inconsistency.

Prerequisites

The source and destination databases are of supported types. For example, 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.
- Data migration: Select Schema Migration and Incremental Data Migration for the Migration Types parameter.
- Data synchronization: By default, Incremental Data Synchronization is selected for the Synchronization Types parameter. You must also select Schema Synchronization.
When you configure a data synchronization or migration task, you can set the Source Objects parameter to an entire database or a schema.

Supported database types

Source database type	Destination database type	Description
MySQL, PolarDB for MySQL, and ApsaraDB RDS for 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.

Note

If the source database of the synchronization or migration task contains a trigger and the destination database is a PostgreSQL, PolarDB for PostgreSQL, or PolarDB for PostgreSQL (Compatible with Oracle) database, DTS prevents data inconsistency caused by trigger synchronization or migration during the synchronization or migration task.
- If the destination database account used by the DTS instance has sufficient permissions, such as a privileged account or a super-privileged account, DTS automatically disables the trigger at the session level after the trigger is synchronized or migrated to the destination database.
- If the destination database account used by the DTS instance does not have the required permissions, make sure that the value of the session_replication_role parameter in the destination database is set to replica during the synchronization or migration task.
For more information about how to configure a synchronization or migration task for other databases that contain triggers, see Configure a data synchronization task for a source database that contains a trigger.

Configure the DTS instance

In the Select Objects step, select Method to Migrate Triggers in Source Database.

Note

If you use the previous Data Management (DMS) console version, select this option in the Configure Objects and Advanced Settings step.

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.
On the right side 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 task, select Schema Synchronization and Incremental Data Synchronization for the Synchronization Types parameter. When you configure a data migration task, 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.