|
|
Depending on the view specification, it may be possible to modify data through a view. However, this isn't always possible. In order for a view to be modifiable via INSERT, UPDATE, or DELETE (which will actually result in modification of the underlying data), certain conditions must be met:
The view can't contain any aggregate functions, such as COUNT or MAX, or use a GROUP BY clause. One exception is that subqueries can use aggregate functions although the values calculated here can't be modified.
The view doesn't contain a TOP clause (SQL Server only).
The view doesn't use DISTINCT.
The view doesn't use calculated columns.
The view must obtain data from one or more tables rather than simply providing explicit values.
This basically restricts view data modification to simple cases but can nonetheless be useful in some circumstances.
You can also utilize something more advanced to make views modifiable, notably triggers and partitioned views, but these are advanced topics that we won't cover here.
The WITH CHECK OPTION clause at the end of a CREATE VIEW statement is used on views that allow data modification to the data visible to the view. For example, if there's a filter on a column such as WHERE ClassID = 4, then you couldn't change the value of this column to any value other than for any record, and you couldn't add a new record with a different value of ClassID. However, you could alter any visible columns that adhere to that condition.
|
|