Adding a nonnull column removes triggers on save changes

Jeff Clark

I'm adding a non-null column to an existing table with data in SQL Source Control 4.0.0.181. My script populates the column with a default value and then removes the default constraint:

ALTER TABLE dbo.METERS ADD
LastUpdatedTS datetime NOT NULL CONSTRAINT DF_METERS_LastUpdatedTS DEFAULT getdate()
GO
ALTER TABLE dbo.METERS SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.METERS
DROP CONSTRAINT DF_METERS_LastUpdatedTS
GO
ALTER TABLE dbo.METERS SET (LOCK_ESCALATION = TABLE)
GO

When I save changes for this table, I get a warning about adding a non-nullable column without a default value having to re-create the table. I hit acknowledge and it saves the table schema, but the two UPDATE triggers that were on the table removed from the table schema file in the working folder. (They are still in the database.) Why?

By the way, for deployment, we have our own migration scripts that run before SQL Compare to handle migration script issues until your next version comes out.

Thank you in advance for you help.

Jeff

Andrew P

Hello,

Jeff and I troubleshot this and the fix was to update to SQL Source Control 4.1.8.41 standard.

If you have an issue similar to this, and updating does not solve the issue, please shoot us an email at support@red-gate.com

It was good working with you Jeff!

Best Regards,