Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

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 ...