Modifying Data Through a View

A view is considered updatable provided that:

• The view contains at least one table in the FROM clause of the view definition; the view cannot be based solely on an expression.
• No aggregate functions (AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, VARP) or GROUP BY, UNION, DISTINCT, or TOP clauses are used in the select list. However, aggregate functions can be used within a subquery that is defined in the FROM clause provided that the derived values generated by the aggregate functions are not modified.
• The view has no derived columns in the select list. Derived columns are result set columns formed by anything other than a simple column expression, such as using functions or addition or subtraction operators.

Before you modify views, consider these guidelines:

• All data modification statements executed against the view must adhere to the criteria set within the SELECT statement defining the view if the WITH CHECK OPTION clause is used in the definition of the view. If the WITH CHECK OPTION clause is used, rows cannot be modified in a way that causes them to disappear from the view. Any modification that would cause this to happen is canceled and an error is displayed.
• Microsoft® SQL Server™ must be able to resolve unambiguously the modification operation to specific rows in one of the base tables referenced by the view. You cannot use data modification statements on more than one underlying table in a single statement. Therefore, the columns listed in the UPDATE or INSERT statement must belong to a single base table within the view definition.
• All the columns in the underlying table being updated that do not allow null values have values specified in either the INSERT statement or DEFAULT definitions. This ensures that all the columns in the underlying table that require values have them.
• The data modified in the columns in the underlying table must adhere to the restrictions on those columns, such as nullability, constraints, DEFAULT definitions and so on. For example, if a row is deleted, all the underlying FOREIGN KEY constraints in related tables must still be satisfied for the delete to succeed.

Additionally, to delete data in a view:

• Only one table can be listed in the FROM clause of the view definition.
• The underlying base table must be updatable.

The READTEXT and WRITETEXT statements cannot be used with text, ntext, or image columns in a view.

To add data through a view

To change data through a view

To delete data through a view