| So far, we have concentrated on using views to query data. Data can also be modified through a view, with a few restrictions. The following points outline the general rules for modifying data through views. -
Modifications to data through views must meet all restrictions that apply to the underlying tables. Modifying data through the view will not circumvent nullibility, rules, constraints, and so on. -
An insert or update through a multitable view cannot affect more than one underlying base table. -
A delete cannot be executed against multitable views. -
Columns consisting of aggregates, computed values, or built-in functions cannot be modified through a view. -
Data cannot be modified through the view if the SELECT statement for the view contains a GROUP BY , DISTINCT , TOP , OR UNION clause. Tables referenced in a view defined with the UNION ALL clause can be modified and will be discussed later in the section, "Partitioned Views." -
Modifications through a view might cause errors if they affect columns not referenced in the view. If you attempt to insert a row through the view and the columns not referenced in the view do not allow NULLS , or do not have default values defined, the insert will fail. -
If a view has been defined with the WITH CHECK OPTION setting specified you cannot perform modifications through the view to the underlying data that would result in that data no longer being accessible through the view. -
The READTEXT or WRITETEXT statements cannot be used with image, text, and ntext columns through a view. Bearing in mind the preceding restrictions, modifying data through a view uses the same INSERT , UPDATE , and DELETE syntax used when data modifications are performed directly on a table. The following query demonstrates changing the contact name in the view contactview . UPDATE contactview SET ContactName = 'Maria Anderson' WHERE ContactName = 'Maria Anders' NOTE The restrictions on modifying data through a view are fairly logical, and some can even be circumvented by defining INSTEAD OF triggers on the view. INSTEAD OF triggers, as their name implies, perform an alternative action than the modification the trigger is defined for. For example, if you are inserting into more than one table of a multitable view, an INSTEAD OF trigger on the view could split the single insert into two separate inserts into the underlying base tables. INSTEAD OF triggers are discussed in further detail in Chapter 29, "Creating and Managing Triggers." | |
