Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 9 Views: Limiting Access to Data

Creating views

Validating a view's selection criteria using with check option

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 would change the value of state from "CA" to "MA". After the update, the row would no longer be visible through the view.

update stores_ca
set state = "CA"
where stor_id = "7066"

Views derived from other views

When a view is created using with check option, all views derived from the "base" view must satisfy its check option. Each row inserted through the derived view must be visible through the base view. Each row updated through the derived view must remain visible through the base view.

Consider the view stores_cal30, which is derived from stores_cal. The new view includes information about stores in California with payment terms of "Net 30":

create view stores_cal30
as select * from stores_ca
where payterms = "Net 30"

Because stores_cal was created using with check option, all rows inserted or updated through stores_cal30 must be visible through stores_cal. Any row with a state value other than "CA" is rejected.

Notice that stores_cal30 does not have a with check option clause of its own. This means that it is possible to insert or update a row with a payterms value other than "Net 30" through stores_cal30. The following update statement would be successful, even though the row would no longer be visible through stores_cal30:

update stores_cal30
set payterms = "Net 60"
where stor_id = "7067"


	Expand Search