Can we write updates and delete requests in views?

Question

In SQL Server 2005, I have some views created using a SELECT . Can we write UPDATE and DELETE expressions in views?

Answer 1

Well, you can remove from the view if that is what you are asking for, but you cannot have a view that removes information. A view is a piece of data from the main tables. If you have permissions, you can perform the same data manipulations in views that you can do directly in the table.

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: Changing Data Through a View ,

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

• Columns that change in the view must refer directly to the underlying data in the columns of the table. They cannot be deduced in any other way, for example, through:

  • Aggregate function (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR and VARP).
  • Calculation; a column cannot be computed from an expression using other columns. Columns formed using set operators (UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT) make up the calculation and also cannot be updated.

• Modified columns cannot affect GROUP BY, HAVING, or DISTINCT clauses.

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

and see the article for stopping ...

Answer 3

What if I do the following?

 Create view table1_View as select * from table1 go delete from table1_view 

I checked and this command will delete all data from table1

Answer 4

In addition to the limited update allowed for the view itself, you can use the INSTEAD OF trigger to make much more significant changes. INSTEAD OF basically allows you to intercept an update or delete and perform almost any changes. References: MSDN article .

Answer 5

More explanations for Srinivas will answer the point ..

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

There is a workaround for this with INSTEAD OF triggers if you can manage your underlying table structure. INSTEAD OF triggers allow you to override an INSERT, UPDATE, or DELETE operation in a view. For example, you can define an INSTEAD OF INSERT trigger in a view to replace the standard INSERT statement.

Suppose you created the view below:

 CREATE VIEW AuthorsNames AS SELECT au_id, au_fname, au_lname FROM authors 

You might want to insert data into columns that are not visible in the view. To do this, create an INSTEAD OF trigger to view the inserts.

 CREATE TRIGGER ShowInsert on AuthorsNames INSTEAD OF INSERT AS BEGIN INSERT INTO authors SELECT address, au_fname, au_id, au_lname, city, contract, phone, state, zip FROM inserted END 

Using this method, you can insert it into several tables, but it becomes more complicated if you are dealing with many base tables. MSDN Link

Answer 6

The answer lies in the fact that the view is an UPDATED VIEW or REQUIRED VIEW.

An updated view is a view that contains all non-zero columns from the base table.

If so, ur update, delete and insert query in the view affects the data in the base table.

Here it is.....

Answer 7

Using Views to Update Data A view can be used in a query that updates data, subject to several limitations. Keep in mind that the view is not a table and does not contain data - the actual modification is always performed at the table level. Views cannot be used as a mechanism to override any constraints, rules, or referential integrity defined in base tables.

Restrictions on updating data using views You can insert, update, and delete rows in a view, subject to the following restrictions:

If a view contains joins between multiple tables, you can insert and update only one table in the view, and you cannot delete rows.

You cannot directly modify data in views based on union queries. You cannot modify data in views that use GROUP BY or DISTINCT statements.

All modified columns are subject to the same restrictions as statements executed directly in the base table.

Lines of text and images cannot be changed using views.

There is no review criteria validation. For example, if all clients who live in Paris are selected in the view and the data is changed to add or edit a row that does not have City = 'Paris', the data will be changed in the base table but not shown in the view if WITH CHECK OPTION not used when defining a view. For More Information Check Article