UPDATING VIEWS WITH CHECK OPTION

When a view is created, rows in the underlying table may be eliminated

through the Create View Where clause, as in

Create View TCO88001V001

as

Select * from TCO88001

Where total_sales > 1000000;

In the case of the above view, any customers with total_sales <=

1000000 will not be selected. However, it may surprise you to learn

that a row can be updated to contain a value that is not in the view

range. For example, a row in the TCO88001V001 view with total_sales

> 1000000 can be updated to contain a value <= 1000000.

In other words, the following statement is valid:

Update TCO88001V001

set total_sales = 900000;

The irony of this is that these rows can never be retrieved again

through that view.

To prohibit this capability, we can use With Check Option to only

allow changes to be made using the same rules that allow rows to be

subsequently selected. That being the case, the following statement

will make the previously shown Update statement invalid:

Create View TCO88001V001

as

Select * from TCO88001

Where total_sales > 1000000

With Check Option;