The comparison algorithm does not work correctly

[garfildalex6]

Hello!
Below I will list the minor differences that the algorithm believes need to be migrated:

1. For some reason, the algorithm does not ignore the difference in the name of the database or the absence of its indication. And also for some reason there is a difference between line breaks. Although the triggers are identical, the algorithm believes that they are different. The screenshot shows an example
   

2. The comparison algorithm does not ignore the difference in quotes
   
   

[drebrez]

Hi @garfildalex6,

the SQL Schema Compare is not analyzing the trigger code to perform a compare, it just compares the definitions returned by the server itself.

It uses this query (https://github.com/TiCodeX/SQLSchemaCompare/blob/master/SQLSchemaCompare.Infrastructure/DatabaseProviders/MicrosoftSqlDatabaseProvider.cs#L267C13-L277C73) to retrieve the trigger definition.

Are you sure that the 2 triggers have been created the same way in source/target databases?
Or maybe they are on 2 different servers and they are configured differently?

[garfildalex6]

In this case, they are located on the same server, but were created through different Mysql GUIs.
It seems to me that such differences could be ignored, since they are not important.

[drebrez]

Hi @garfildalex6,

to see the trigger definition it does the query "SHOW CREATE TRIGGER {context.DatabaseName}.{trigger.Name}", this is then used for the comparison.

I are probably right that such differences could be ignored, but that would require to parse and break down the trigger definition in all the possible ways, and this not something easy to implement.

Do you know if there is a way to retrieve all the separate info about a trigger? in that case it might be possible to improve the comparison logic

[garfildalex6]

Do you know if there is a way to retrieve all the separate info about a trigger?

Unfortunately, I don’t know, but I will look for such information. If I find something, I'll let you know.
Thank you!

[garfildalex6]

@drebrez
I found this request:
SELECT ACTION_STATEMENT FROM information_schema.TRIGGERS WHERE TRIGGER_NAME = 'trigger name' AND TRIGGER_SCHEMA = 'database name';

There is all the information on each trigger separately and you can assemble the trigger script as you wish.
I think this will be useful!