How to customize SQL Scripts generated by Visual Studio

Question

So we are attempting to implement DDL triggers on our databases to track DDL changes for our developers, and have that working great. However, we've run into a snag with our Visual Studio 2013/2015 developers. When they edit a table/proc/anything from within Visual Studio, it will disable the ddl trigger, make the change, and re-enable the trigger, which makes the trigger pointless.

I have found that I can create a SQL Database project, navigate to Project > Properties > Debug > Advanced, and uncheck the "Disable and Reenable DDL Triggers". This setting gets stored in the Project.SQLProj.User file. And the scripts will stop adding in the Disable/Enable trigger portions.

However, when devs just go to Server Explorer/SQL Server Object Explorer and pull up a database table that way, any time they make a change and run "Update" and generate the script, lo and behold there is the Disable/reenable ddl trigger statement within the script. I've combed over visual studio help files, the googles, our local VS gurus, and nobody seems to know where the settings for these on the fly/non-project scoped scripts live. Any help would be appreciated.

Answer 1

When you publish the options below are used. I'm unaware of the impact the settings in the .user file has

• If you are using publish dialog window then click on advanced and then deselect

  Disable And Reenable Ddl Triggers
  

• If you are using a publish profile file something like yourDb.publish.xml then ad this to your file

  <DisableAndReenableDdlTriggers>False</DisableAndReenableDdlTriggers>
  

• If you are doing this using SQLPackage.exe you can also specify /p:DisableAndReenableDdlTriggers=false

Answer 2

Welp, I found a workaround to the issue. I still can't get Visual Studio to not disable the triggers by default, but I worked around it by creating a server level DDL trigger.

A Server level DDL trigger will audit both server and database DDL, and won't get disabled when VS issues the disable database ddl trigger statement.