0

We found a bug in the SQL Server Management Studio. When you change any table property that requires the table to be dropped and re-created, all your triggers are gone. It must be done within the designer. The designer needs to have "Prevent saving changes that require re-creation" option disabled. In general, every time such a change occur the table is copied, dropped and recreated with the new set of properties. Unfortunately, the process deletes your triggers.

We checked every version of SQL SMS we have, and this are: 2008, 2012, 2014 and 2016. The bug is fixed only in the 2016 version. It might be important, that we used to have Microsoft SQL Server 2000 (32 bit) database and upgraded it to Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64).

We found the bug thanks to our database documentation. We saw there are no triggers in the table and our first thought was that there were none from the very beginning. A quick look at the documentation was enough to see that we are wrong. We recreate the process in our database. You can watch in on the video below:

https://www.youtube.com/watch?v=1VWrPBOC5rA

Did you encounter it in any other SSMS version and if yes, then on which database version?

Do you know any workaround for it?

Improve this question
2
  • 1
    I think the only workaround is not to use GUI but use T-SQL instead. How often do you change your table in way it need to be recreated?!? I remember, it does not recreate even foreign key constraints, and you want it to recreate triggers!!!
    – sepupic
    Commented Aug 3, 2017 at 12:56
  • As a general rule, I would recommend use the "Generate Scripts" option under Tasks to keep the SQL Commands necessary to create an empty copy of your database. I would also generally prefer to modify my database by running a script, rather than using the GUI to do it. A script you've tested in your development environment should work the same in production; through the GUI, you've got to be certain you've selected all the same options, named things the same way, etc.
    – RDFozz
    Commented Aug 3, 2017 at 14:26

1 Answer 1

Reset to default
4

You shouldn't be doing those changes in the GUI anyway but in T-SQL it's the only way you can reliably publish changes from dev > test > prod and it's the only way you can have source control on your database structure.

That being said, this happens when you change things that require the table to be recreated and is not a bug but documented behaviour.

Please see Error message when you try to save a table in SQL Server: "Saving changes is not permitted" which clearly states (emphasis mine):

This problem occurs when the Prevent saving changes that require the table re-creation option is enabled, and you make one or more of the following changes to the table:

  • You change the Allow Nulls setting for a column.
  • You reorder columns in the table.
  • You change the column data type.
  • You add a new column.

When you change a table so that you alter the metadata structure of the table, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table. If you enable the Prevent saving changes that require the table re-creation option in the Designer section of the SQL Server Management Studio (SSMS) Options window, you receive the error message that is mentioned in the "Symptoms" section.

The work-around in that article is exactly what you should be doing:

To work around this problem, use Transact-SQL statements to make the changes to the metadata structure of a table.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.