Use the WITH CHECK OPTION keywords

To avoid updating a row of a view so that it no longer satisfies the conditions of the view, add the WITH CHECK OPTION keywords when you create the view. This clause asks the database server to test every inserted or updated row to ensure that it meets the conditions set by the WHERE clause of the view. The database server rejects the operation with an error if the conditions are not met.

In the previous example, the view named response is defined as the following example shows:

CREATE   VIEW   response (user_id, received,  resolved,  duration) AS
   SELECT user_id,call_dtime,res_dtime,res_dtime  - call_dtime
      FROM cust_calls
      WHERE user_id = USER

You can update the user_id column of the view, as the following example shows:

UPDATE response SET user_id = 'lenora'
   WHERE received BETWEEN TODAY AND TODAY - 7

The view requires rows in which user_id equals USER. If user tony performs this update, the updated rows vanish from the view. You can create the view, however, as the following example shows:

CREATE   VIEW    response  (user_id, received, resolved,duration) AS
   SELECT user_id, call_dtime, res_dtime, res_dtime - call_dtime
      FROM cust_calls
      WHERE user_id = USER
WITH CHECK OPTION

You can use the WITH CHECK OPTION feature to enforce any kind of data constraint that can be stated as a Boolean expression. In the following example, you can create a view of a table for which you express all the logical constraints on data as conditions of the WHERE clause. Then you can require all modifications to the table to be made through the view.

CREATE VIEW order_insert AS
   SELECT * FROM orders O
      WHERE order_date = TODAY -- no back-dated entries
         AND EXISTS -- ensure valid foreign key
            (SELECT * FROM customer C
               WHERE O.customer_num = C.customer_num)
         AND ship_weight < 1000 -- reasonableness checks
         AND ship_charge < 1000
WITH CHECK OPTION