Microsoft® SQL Server® 2012 Bible

285

Chapter 11: Projecting Data Through Views

11

dbo.vComponentsProductSubCats

Results:

ProductCategoryID ProductSubCatgory

------------------- ---------------------

1 Mountain Bikes

1 Road Bikes

1 Touring Bikes

If someone adds a Bike Pedal Accessory and inserts using the view without the

CHECK OPTION, the INSERT is permitted.

INSERT INTO vComponentsProductSubCats(ProductCategoryID,

ProductSubCategory)

VALUES(2, 'Bike Pedal');

(1 row(s) affected)

The INSERT worked, and the new row is in the database, but the row is not visible through

the view because the WHERE clause of the view fi lters out the inserted row. This phenom-

enon is called disappearing rows:

SELECT ProductCategoryID, ProductSubCategory FROM

dbo.vComponentsProductSubCats

Results:

ProductCategoryID SProductSubCatgory

------------------ ---------------------

1 Mountain Bikes

1 Road Bikes

1 Touring Bikes

If the purpose of the view were to give users that managed the Bikes Subcategories access

to those subcategories alone, then the view failed. Although they can see only the Bike

Subcategories, they successfully modifi ed another Categories Product Subcategories. The

WITH CHECK OPTION would have prevented this fault.

Protecting the Data

A view with a WHERE clause and the WITH CHECK OPTION can protect the data from unde-

sired inserts and updates.

The following code can back out the previous INSERT and redo the same scenario, but this

time the view includes the WITH CHECK OPTION:

DELETE FROM Production.ProductSubcategory

WHERE Name = 'Bike Pedal';

GO

USE AdventureWorks2008R2

GO

ALTER view vComponentsProductSubCats

AS

c11.indd 285c11.indd 285 7/30/2012 4:41:11 PM7/30/2012 4:41:11 PM

http://www.it-ebooks.info