You are a database developer on an instance of SQL Server 2008. Your database contains a Prospect table defined using the following Transact-SQL:
CREATE TABLE Prospect(
ID int IDENTITY(1,1) PRIMARY KEY,
TerritoryID int NULL,
CompanyName varchar(35) UNIQUE,
Type char(1) NOT NULL,
Rating tinyint,
EstRevenue money);
Your Prospect table currently contains the following data:
You use the following Transact-SQL statement to create a view:
CREATE VIEW ProspectView
AS
SELECT * FROM Prospect
WHERE Rating BETWEEN 1 and 3
WITH CHECK OPTION;
Assuming appropriate permissions, which two UPDATE statements will subsequently execute successfully and update a row in the Prospect table? (Choose two.)
A. UPDATE Prospect
SET Rating = 9
WHERE ID = 1;
B. UPDATE ProspectView
SET Rating = 5
WHERE ID = 3;
C. UPDATE ProspectView
SET Rating = 2
WHERE ID = 2;
D. UPDATE ProspectView
SET Rating = 1
WHERE ID = 3;
E. UPDATE ProspectView
SET Rating = 8
WHERE ID = 2;
Correct Answer: AC
Explanation/Reference:
In this scenario, you created a view that included the WITH CHECK OPTION clause. The WITH CHECK OPTION clause is used to ensure that no data modifications can be made through a view that would cause the underlying data to violate the view’s definition. The first statement directly updates the Prospect table, and no CHECK constraint is defined on the Rating column of the Prospect table. Therefore, the statement executes and updates the Rating value for the first row in the table. The second statement updates the Prospect table using the ProspectView view. The row with an ID value of 2 is visible through the view, and the Rating value specified in the SET clause conforms to the WHERE clause of the view’s definition. Therefore, the statement executes and updates the Rating value for the second row in the table.
The following two statements will execute successfully, but will not update a row in the Prospect table because the row with an ID value of 3 is not visible through the ProspectView view:
UPDATE ProspectViewSET Rating = 5 WHERE ID = 3;
UPDATE ProspectViewSET Rating = 1 WHERE ID = 3;
The following statement will generate an error because it uses the view to update the table, but attempts to set the Rating column to a value that does not conform to the WHERE clause of the view’s definition:
UPDATE ProspectView SET Rating = 8 WHERE ID = 2;
If you executed this statement, the following message would be displayed:
Error message:Msg 550, Level 16, State 1, Line 1The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans aview that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
