Visual Studio 2010 deployment script creates objects in wrong order - trigger after stored procedure

Question

I created a view that selects from multiple tables in a complex query.

• Due to the complexity, I created insert/update/delete INSTEAD OF triggers to handle these operations on the view.
• I also created stored procedures that update the view.

When I build the VS2010 database project, the SQL script contains the CREATE commands in this order: (1) the view, (2) the stored procedures, and then (3) the triggers.

Now, when I run the deployment script, I get an error:

*** SQL01268    D:\scripts\vs2010\Database.sql (28494,0).Net SqlClient Data Provider:
Msg 4405, Level 16, State 1, Procedure MyViewSave, Line 18 View or function 'MyView' is
not updatable because the modification affects multiple base tables

If the triggers would be created before the stored procedure, there would be no problem.

How can I make sure the triggers are created before the stored procedures?

Thanks!

Answer 1

You may have run into a known issue with Visual Studio 2010:

• Connect request
• SO Question
• MSDN forum thread

That said, Microsoft recommends you help Visual Studio detect the object dependencies it seems to be ignoring in its deployment script by:

schema-qualify[ing] the names of objects that are involved in dependent relationships.

So if everything you are developing sits in the dbo schema, make sure to prepend the proc and view names giving you trouble with their schema name. For example: dbo.MyView instead of just MyView. Visual Studio has a "Fully Qualify Name" feature under the Refactor menu when you right-click on an object that'll help you do that very quickly.

If that doesn't help, give Google a spin and take a look at some of the workarounds suggested on the pages linked above. Let us know what ended up working for you.