If a trigger 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.

Background information

If DDL operations to be synchronized or migrated contain triggers and the destination database in an incremental data synchronization or migration task is of a specific database type, you can configure the Method to Migrate Triggers in Source Database parameter in the Advanced Settings step.
  • If you set this parameter to Manual Migration, Data Transmission Service (DTS) generates an alert that displays object ignored due to incremental migration is enabled 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 the incremental data synchronization or migration task 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. 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.

Prerequisites

  • The database type of the destination database is MySQL, PolarDB for MySQL, AnalyticDB for MySQL V3.0, or ApsaraDB RDS for MariaDB.
  • An incremental data synchronization or migration task is included in the DTS instance.

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 you how to manually synchronize triggers from the source database.
  1. Go to the Data Synchronization Tasks page.
    1. Log on to the DMS console.
    2. In the top navigation bar, click DTS.
    3. In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.
  2. From the drop-down list to the right of Data Synchronization Tasks, select the region in which your data synchronization instance resides.
    Note If you use the new DTS console, select the region in which your data synchronization instance resides in the top navigation bar.
  3. Click the ID of the instance that you want to manage.
  4. Optional: In the left-side navigation pane, click Task Management.
  5. In the Progress section, click the Incremental Write module.
  6. 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:

SQL statement used to 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;
SQL statement used to 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;