Modifying Data Through a View

You can modify data through a view in these ways:

• Use INSTEAD OF triggers with logic to support INSERT, UPDATE and DELETE statements.
  
  
• Use updatable partitioned views that modify one or more member tables.

If a view does not use an INSTEAD OF trigger or is not an updatable partitioned view, it can still be updatable provided that:

If a view does not use an INSTEAD OF trigger or is not an updatable partitioned view, it can still be 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 defined in the FROM clause provided that the derived values generated by the aggregate functions are not modified.

  Note  Partitioned views using the UNION ALL operator can be updatable.

• No derived columns are used in the select list. Derived columns are result set columns formed by anything other than a simple column reference.

Guidelines for Modifying Data Through a View

Before you modify data through a view without using an INSTEAD OF trigger or an updatable partitioned view, 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.
  
  
• 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 that are being updated and 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.
  
  
• A distributed partition view (remote view) cannot be updated using a keyset-driven cursor. This restriction can be resolved by declaring the cursor on the underlying tables and not on the view itself.

Additionally, to delete data in a view:

• Only one table can be listed in the FROM clause of the view definition.

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

To add data through a view

Transact-SQL

To change data through a view

Transact-SQL

To delete data through a view

Transact-SQL