can we write update and delete queries in views?

Question

hi,

i have a doubt, in sql server 2005 views. views created by using select statement.

can we write update and delete statements in views .

Answer 1

Well you can delete from a view if that is what you are asking, but you can't have a view that deletes information. The view is a portion of data from the underlying tables. Provided that you have permissions, you can do the same data manipulation in views that you can do to a table directly.

So you can do something like:

DELETE FROM my_View WHERE id = 3;

When to use views
What is a view

Answer 2

from this MSDN article: Modifying Data Through a View,

• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

• The columns that are being modified in the view must reference the underlying data in the table columns directly. They cannot be derived in any other way, such as through:

  • An aggregate function (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR and VARP).
  • A computation; the column cannot be computed from an expression using other columns. Columns formed using set operators (UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT) amount to a computation and are also not updatable.

• The columns that are being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.

• TOP cannot be used anywhere in the select_statement of the view when WITH CHECK OPTION is also specified.

and see the article for remaining ...

Answer 3

In addition to the limited updating allowed on the view itself, you can use an INSTEAD OF trigger to perform much more involved changes. INSTEAD OF basically lets you intercept the update or delete, and perform almost any change. References: MSDN Article.