Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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