The WITH CHECK OPTION Clause

for RuBoard

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.

for RuBoard