Home > Articles > Data > SQL Server

  • Print
  • + Share This

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.

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.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.