Data Transmission Service:Synchronize or migrate triggers from the source database

Prerequisites

Before you begin, make sure that:

• The source and destination database types are supported. See the Supported database types section.

• Both schema synchronization/migration and incremental data synchronization/migration are selected when you configure the DTS instance:

  • Data migration: Select Schema Migration and Incremental Data Migration for Migration Types.

  • Data synchronization: Incremental Data Synchronization is selected by default for Synchronization Types. Also select Schema Synchronization.

• Source Objects is set to an entire database or schema.

Supported database types

If the destination database is PostgreSQL, PolarDB for PostgreSQL, or PolarDB for PostgreSQL (Compatible with Oracle), DTS prevents data inconsistency automatically:

If the DTS instance uses a privileged or super-privileged destination database account, DTS disables the trigger at the session level after the trigger is synchronized or migrated.

If the account does not have sufficient permissions, set the session_replication_role parameter to replica in the destination database for the duration of the task. For other database types that contain triggers, see Configure a data synchronization task for a source database that contains a trigger.

Choose a migration method

In the Configure Objects step of the DTS instance setup, set Method to Migrate Triggers in Source Database.

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

How automatic migration works

When you select Automatically Migrate, DTS copies the trigger code from the source database, wraps the trigger body in a control statement, and writes the modified trigger to the destination database.

Source database trigger:

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

Destination database trigger (after DTS modifies it):

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;

Manually migrate triggers

The steps for manually migrating triggers are the same as for manually synchronizing them. The following example shows manual synchronization.

1. Log on to the Data Management (DMS) console. In the top navigation bar, click Data + AI. In the left-side navigation pane, choose DTS (DTS) > Data Synchronization.

   You can also go directly to the Data Synchronization Tasks page of the new DTS console. For console layout options, see Simple mode and Customize the layout and style of the DMS console.

2. On the right side of Data Synchronization Tasks, select the region where the data synchronization instance resides.

   In the new DTS console, select the region from the top navigation bar.

3. Find the DTS instance and click the instance ID.

4. (Optional) In the left-side pane, click Task Management.

5. In the Progress section, click the Incremental Write module.

6. On the Basic Information tab, click Migrate Triggers.

   If Migrate Triggers is not displayed, no trigger was encountered during schema synchronization or migration. To verify the trigger synchronization result, check the Schema Migration3 module in the Progress section.

FAQ

How do I select schema and incremental sync/migration types when configuring a DTS instance?

For a data synchronization task, select Schema Synchronization and Incremental Data Synchronization for Synchronization Types. For a data migration task, select Schema Migration and Incremental Data Migration for Migration Types.

How do I complete incremental data synchronization or migration?

Terminate, reset, or release the DTS instance. For details, see Terminate a DTS instance, Reset a DTS instance, and Release DTS instances.