- Creating and Optimizing Views
- Creating and Modifying Views
- Using Views to Update Data
- Indexed Views
- Optimizing Views and Queries
- Summary
Using Views to Update Data
A view can be used in a query that updates data, subject to a few restrictions. Bear in mind that a view is not a table and contains no data—the actual modification always takes place at the table level. Views cannot be used as a mechanism to override any constraints, rules, or referential integrity defined in the base tables.
Restrictions on Updating Data Through Views
You can insert, update, and delete rows in a view, subject to the following limitations:
-
If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows.
-
You can't directly modify data in views based on union queries. You can't modify data in views that use GROUP BY or DISTINCT statements.
-
All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base table.
-
Text and image columns can't be modified through views.
-
There is no checking of view criteria. For example, if the view selects all customers who live in Paris, and data is modified to either add or edit a row that does not have City = 'Paris', the data will be modified in the base table but not shown in the view, unless WITH CHECK OPTION is used when defining the view.
Using WITH CHECK OPTION
The WITH CHECK OPTION clause forces all data-modification statements executed against the view to adhere to the criteria set within the WHERE clause of the SELECT statement defining the view. Rows cannot be modified in a way that causes them to vanish from the view. Listing 9.12 creates a view showing customers from Paris using the WITH CHECK OPTION statement.
Listing 9.12 Creating a View Using WITH CHECK OPTION
CREATE VIEW vwCustomersParis AS SELECT CompanyName, ContactName, Phone, City FROM Customers WHERE City = 'Paris' WITH CHECK OPTION
The following Transact-SQL statement attempting to update data by moving everyone from Paris to Lyons will fail because Lyons does not meet the criteria defined in the view. If you did not have WITH CHECK OPTION defined, the UPDATE statement would succeed, and a requery of the view would return no rows. Here's the statement:
UPDATE vwCustomersParis SET City = 'Lyons'
You may have noticed in Access that placing criteria on the RecordSource query of a form limits the records that are displayed in the form when you open it, but it doesn't limit what records can be added in the form. Using an Access project or an Access database with a form bound to a view that contains WITH CHECK OPTION would allow you to automatically have the criteria enforced for new or updated records.
Updating Views with Joins
A view that contains a join will only be updateable on one side of the join, unless an INSTEAD OF trigger is created on the view. INSTEAD OF triggers are discussed in the next section. For example, the view shown in Listing 9.13 is based on a join between the Categories and Products tables.
Listing 9.13 A View with a Join
CREATE VIEW vwCategoriesProducts AS SELECT Categories.CategoryName, Products.ProductID, Products.ProductName FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID
The first few rows of the output for this view are shown in Figure 9.10.
){kind=link}
Figure
9.10
A view selecting data from both the Categories and Products tables.
The following UPDATE statement will work because it's only affecting the Products table's side of the join:
UPDATE vwCategoriesProducts SET ProductName = 'Chay' WHERE ProductID = 1
This UPDATE statement will also work because only affects the Categories table's side of the join:
UPDATE vwCategoriesProducts SET CategoryName = 'Drinks' WHERE ProductID = 1
However, the following UPDATE statement attempting to modify columns in both the Products and Categories tables won't work (you'll get the error "View or function 'vwCategoriesProducts' is not updateable because the FROM clause names multiple tables"):
UPDATE vwCategoriesProducts SET ProductName = 'Chay', CategoryName = 'Drinks' WHERE ProductID = 1
The Trouble with Updateable Views
In general, you'll want to make views that use joins read-only. Allowing updates in views with joins is likely to confuse users because it's not intuitive that they can't update different columns on the same row. After all, it looks like the same table to them.
If you want users to be able to use views to update data, base the view on a single table, or use a stored procedure to perform the update.
Updating Data Using User-Defined Inline Functions
The rules for updating user-defined inline functions are the same as they are for views. Consider these three statements:
SELECT * FROM dbo.fnProductSelectInline() WHERE ProductID = 1 UPDATE dbo.fnProductSelectInline() SET UnitPrice = 20 WHERE ProductID = 1 SELECT * FROM dbo.fnProductSelectInline() WHERE ProductID = 1
The first statement selects the data from the fnProductSelectInline() function, the second statement updates it, and the third statement selects the new value. Here's the output for the two SELECT statements demonstrating that the function worked:
ProductID ProductName UnitPrice ----------- ---------------------------------------- --------------------- 1 Chai 19.0000 ProductID ProductName UnitPrice ----------- ---------------------------------------- --------------------- 1 Chai 20.0000
An inline function that has a parameter can also be used to update data, with the parameter limiting the scope of the update. Here is a function where a parameter is used to return a single row from the Products table:
CREATE FUNCTION fnProductInlineParam (@ProductID int) RETURNS TABLE AS RETURN (SELECT ProductID, ProductName, UnitPrice FROM Products WHERE ProductID = @ProductID)
Call the function as follows to update the price of ProductID 5 to 29.95:
UPDATE dbo.fnProductInlineParam(5) SET UnitPrice = 29.95
Only the one product with the ProductID of 5 will have its price changed.
Using INSTEAD OF Triggers to Update Non-Updateable Views
An INSTEAD OF trigger on a view allows you to get around many of the restrictions on updating views. For example, only one table in a view with multiple joined tables can be updated. An INSTEAD OF trigger can support inserts, updates, and deletes that reference data in more than one table. INSTEAD OF triggers also allow you to code more complex logic than is normally supported in views; and they let you work with time stamp data, computed columns, and identity columns.
The following view selects the CompanyName values in a UNION query between the Customers and Suppliers tables:
CREATE VIEW vwUnionCustomerSupplier AS SELECT CompanyName, 'Customer' AS Type FROM Customers UNION ALL SELECT CompanyName, 'Supplier' AS Type FROM Suppliers
Normally a UNION query is not updateable. However, an INSTEAD OF trigger lets you update the tables involved because it can execute code instead of the default action (UPDATE). The trigger makes use of the inserted table, which contains the new value, to insert data into the appropriate table based on the Type value. It also makes use of the deleted table, which contains the old value, to find the correct record in the base table.
CREATE TRIGGER trigUnion ON vwUnionCustomerSupplier INSTEAD OF UPDATE AS BEGIN SET NOCOUNT ON DECLARE @DelName nvarchar(50) IF (SELECT inserted.Type FROM inserted) Is Null RETURN SELECT @DelName = deleted.CompanyName FROM deleted IF (SELECT inserted.Type FROM inserted) = 'Company' UPDATE Customers SET CompanyName = (SELECT CompanyName FROM inserted) WHERE Customers.CompanyName = @DelName ELSE UPDATE Suppliers SET CompanyName = (SELECT CompanyName FROM inserted) WHERE Suppliers.CompanyName = @DelName END
This allows the following UPDATE statement to update the Customers table with a new company name:
UPDATE vwUnionCustomerSupplier SET CompanyName = 'Around the Block' WHERE CompanyName = 'Around the Horn'
As you can see, INSTEAD OF triggers on views can make them very powerful indeed, allowing actions that would not normally be permitted. You could also use INSTEAD OF triggers to call stored procedures to perform the requested data modification.