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?
Best Answer
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):
The work-around in that article is exactly what you should be doing: