Triggers section of 'SQL Differences' in v 6.1.0.53

LeeC

Please help me to find where I set SQL Compare to ignore trigger DDL order. It indicates differences in the Triggers section of my one table, but I cannot make sense of the supposed differences.

There are approximately 600 lines of Triggers DDL, which I believe should match on a per trigger basis, but SQL Compare indicates that no lines match and it shows a real mess of DDL in differing trigger orders on each side of the comparison.

Sincerely,
Lee

Babs.egun

SQL Compare does not have an option to ignore trigger DDL order

Using "disable DDL triggers during synchronization" button allows you to disable DDL triggers during synchronization as they can cause problems and reenable them after synchronization.

LeeC

So, if I understand correctly, you are saying that since SQL Compare does not properly compare trigger SQL, I should tell it to not synchronize the very SQL that I'm trying to reconcile--because it compares the trigger SQL incorrectly in the first place?

Why does the app even try to compare triggers if it cannot do it properly?

Perhaps the product name should be changed to "All But Trigger SQL Compare"

JonathanWatts

Lee,

I think your problem stems from the fact that the SQL Compare carries out two comparisons of objects. The status of objects like a table's triggers is determined by a semantic comparison of the objects and the result of which is displayed in the main grid, this will take things like the order of triggers into account. This means that any script generated will be correct, and will only change what is necessary to be changed rather than dropping and recreating all your triggers.

However, the SQL Differences panel carries out a straight textual comparison of objects. This is done primarily for speed and secondary because 90% of semantic differences are also textual differences.

The drawback of this approach is that whilst it works fine for functions, and stored procedures, if SQL Server returns objects like a table's triggers in a different order in each database then the SQL Differences Panel will display false positives.

We are considering improving the current behaviour in SQL Compare, either by allowing triggers to be compared as separate objects or adding semantic comparison to the SQL Differences Panel

Regards,

Jonathan

BorisNikolaevich

I vote for triggers to be compared as separate objects. After all (and I know I'm just instructing people who already know this), Triggers are just stored procedures tied to a specific table's events. I don't think they should be treated as "extended properties" of the table.

JonathanWatts

Hi Boris,

Thanks for your vote on this and understand your point of view. It is a very popular request and as I say it is something we are considering for future releases of SQL Compare.

Thanks,

Jonathan