SSDT Schema Comparison needs to handle Database Triggers first

SSDT Schema Comparison needs to handle Database Triggers first by Mark Freeman

Active

Type:	Bug
ID:	773683
Opened:	12/6/2012 8:29:10 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When generating a publish script from a schema comparison where there has been a change to a database trigger, the ALTER for that trigger needs to be the first statement in the main body of the script, do that it is in effect when all of the other statements run. It is currently generated last, just before the calls to sp_refreshsqlmodule.

I am using the SQL Server Data Tools 10.3.20905.0 in Visual Studio 2010 SP1, comparing the project schema with a SQL Server 2005 SP4 database.

Details (expand)

English

SQL Server 2005 SP4

Developer Tools (SSDT, BIDS, etc.)

Not Applicable

US English

Create a database trigger (ON DATABASE FOR CREATE_PROCEDURE, CREATE FUNCTION) in the project that prevents or causes an error on the creation of functions. (In my case, I was doing a GRANT EXECUTE on the new function.)
Compare schema, generate script, update database using the script.
Change the database trigger in the project.
Create a new function in the project (have it return a table, which means that you can't GRANT EXECUTE on it).
Compare schema, generate script.
Examine the script.

Note that the ALTER for the database trigger occurs *after* the CREATE FUNCTION. This means that when the procedure is created, the *old* version of the database trigger will be in effect.

The ALTER for the database trigger should occur *before* the CREATE FUNCTION. This means that when the procedure is created, the *new* version of the database trigger will be in effect.

0 attachments

Hey Mark,

Thanks for your feedback around the incorrect scripting/deployment ordering when dealing with modifying database level triggers in this case. The SSDT/DACFx team is not currently pursuing a fix for this issue, however, we have an item tracking it should it come up for future consideration. The workaround for this issue is to manually alter the generated deployment script so that the database trigger is refreshed in the appropriate order.

Thanks,
Adam Mahood
Program Manager
SQL Server Database Systems