Copy tables from the data source- Trigger Options

At the tab Trigger Options of the Advanced Options Dialog you can setup and use the unique feature of Triggers on import source.

The essence of Triggers is that they allow you to introduce changes in a source row after it has been successfully imported to your destination MySQL table.

The options are:

  • To modify imported rows in the source. For this, check the option Update source row, then specify the row in the column Source of the below grid and next to it define the value you need to assign. As a value you can use nominal values (like 12, 'imported', etc.) or single-row functions (like SYSDATE, REPLACE, etc.)
    In the box under the grid you can see the resulting statement. With the button Parse you can check if it is correct. Please remember that the values must be syntactically correct, that is use quotes for strings, etc.
    With the triggers functionality you can even use functions and expressions. The syntax used here and name and use of functions must follow the syntax of the SQL-version ("dialect") of the source database and the related ODBC-driver. A few simple examples of the use of this:
    To delete imported rows from the source. For this, check the option Delete source row.

    * you can increment a numerical field (call it "number_of_imports") by adding 1 to that field.

    * you can write current date and time to a datetime variable type using some datetime() function. This is particularly useful for incremental imports.

    * you can use conditionals ("if ..") and any other language construction allowed in expressions with the source database. A simple example: use it to reset negative values to 0 (zero) and leave positive values as they ar

  • The most obvious application of this feature is that you can mark the rows that have already been imported by assigning a certain value to some column directly in the source. Additionally, combined with the use of a SQL Where statement, triggers allow arranging incremental import sessions.

Note: This feature is available in SQLyog Enterprise and Ultimate.