Normally, Adaptive Server does not check insert and update statements on views to determine whether the affected rows are within the scope of the view. A statement can insert a row into the underlying base table, but not into the view, or change an existing row so that it no longer meets the view’s selection criteria.
When you create a view using the with check option clause, each insert and update through the view is validated against the view’s selection criteria. All rows inserted or updated through the view must remain visible through the view, or the statement fails.
Here is an example of a view, stores_ca, created using with check option. This view includes information about stores located in California, but excludes information about stores located in any other state. The view is created by selecting all rows from the stores table for which state has a value of “CA”:
create view stores_ca as select * from stores where state = "CA" with check option
When you try to insert a row through stores_ca, Adaptive Server verifies that the new row falls within the scope of the view. The following insert statement fails because the new row would have a state value of “NY” rather than “CA”:
insert stores_ca
values ("7100", "Castle Books", "351 West 24 St.", "New York", "NY", "USA", "10011", "Net 30")
When you try to update a row through stores_cal, Adaptive Server verifies that the update will not cause the row to disappear from the view. The following update statement fails because it attempts to change the value of state from “CA” to “MA.” If this update was allowed, the row would no longer be visible through the view.
update stores_ca set state = "MA" where stor_id = "7066"
