Drops and Creates a Trigger Before the table is Altered

aselagun

Hi All

We use SQL Compare to compare the versions of databases and generate the deployment script.

How ever recently I noticed, that it DROPS and CREATES a TRIGGER before the table itself is ALTERED.

So it fails because it tries to create a TRIGGER on COLMN that does not EXIST.

For an Example it Generates the SCRIPT in the Following Order

IF OBJECT_ID(N'[dbo].[OnPathwaySectionsUpdateRefreshBI]', 'TR') IS NOT NULL
DROP TRIGGER [dbo].[OnPathwaySectionsUpdateRefreshBI]
GO
PRINT N'Dropping trigger [dbo].[OnAddressHistory] from [dbo].[tblAddress]'
GO



IF OBJECT_ID(N'[dbo].[OnAddressHistory]', 'TR') IS NULL
EXEC sp_executesql N'
CREATE TRIGGER [dbo].[OnAddressHistory]
........
TRIGGER CODE
...


IF COL_LENGTH(N'[dbo].[tblAddressHistory]', N'fldPlanningRegion') IS NULL
ALTER TABLE [dbo].[tblAddressHistory] ADD[fldPlanningRegion] [uniqueidentifier] NULL
GO

Could you please let me know what's causing this and How to Fix this.

Thanx in Advance!

Brian Donahue

Do you absolutely need the "check object existence" and/or "drop and create instead of alter" options? It may work with these off. There seems to be a bug in SQL Compare that causes this problem sometimes with this option on.