Update/alter data from view?
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.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:A view selecting data from both the Categories and Products tables.
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.
No comments:
Post a Comment