The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources]

The WITH CHECK OPTION Clause

An updatable view can be created so that it checks updates for compliance with its WHERE clause, if it has one. This prevents rows added via the view from "vanishing" when the view is requeried because they don't meet its selection criteria. To set up a view this way, use the WITH CHECK OPTION clause when you create it (Listing 9-10):

Listing 9-10 WITH CHECK OPTION controls the type of data a view will accept.

CREATE VIEW CALIFORNIA_AUTHORS AS
SELECT *
FROM authors
WHERE State='CA'
WITH CHECK OPTION

This particular example ensures that any author that's added via the view resides in California. For example, the statement in Listing 9-11 fails because of WITH CHECK OPTION:

Listing 9-11 WITH CHECK OPTION refuses rows that fall outside the view's filter criteria.

INSERT CALIFORNIA_AUTHORS
VALUES ('867-53-09EI','Henderson','Ken',
'972 555-1212','57 Riverside','Dallas','TX','75080',1)
Server: Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the 
target view either specifies
WITH CHECK OPTION or spans a view that specifies
 WITH CHECK OPTION and one or
more rows resulting from the operation did not 
qualify under the CHECK OPTION constraint.
The statement has been terminated.

This also applies to updates. If an update you make through a view that has WITH CHECK OPTION enabled would cause the row to fail the view's WHERE criteria, the update will be rejected.