Modifying with a View

You can modify views as if they were tables. Some views can be modified and others not, depending on their SELECT statements. The restrictions are different, depending on whether you use DELETE, UPDATE, or INSERT statements.

A view is modifiable if the SELECT statement that defined it did not contain any of the following items:

• A join of two or more tables
• An aggregate function or the GROUP BY clause
• The DISTINCT keyword or its synonym UNIQUE
• The UNION keyword

When a view avoids all these restricted features, each row of the view corresponds to exactly one row of one table.

Deleting with a View

You can use a DELETE statement on a modifiable view as if it were a table. The database server deletes the proper row of the underlying table.

Updating a View

You can use an UPDATE statement on a modifiable view. However, the database server does not support updating any derived column. A derived column is a column produced by an expression in the select list of the CREATE VIEW statement (for example, order_date + 30).

The following example shows a modifiable view that contains a derived column and an UPDATE statement that can be accepted against it:

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;

UPDATE response SET resolved = TODAY
   WHERE resolved IS NULL;

You cannot update the duration column of the view because it represents an expression (the database server cannot, even in principle, decide how to distribute an update value between the two columns that the expression names). But as long as no derived columns are named in the SET clause, you can perform the update as if the view were a table.

A view can return duplicate rows even though the rows of the underlying table are unique. You cannot distinguish one duplicate row from another. If you update one of a set of duplicate rows (for example, if you use a cursor to update WHERE CURRENT), you cannot be sure which row in the underlying table receives the update.

Inserting into a View

You can insert rows into a view, provided that the view is modifiable and contains no derived columns. The reason for the second restriction is that an inserted row must provide values for all columns, and the database server cannot tell how to distribute an inserted value through an expression. An attempt to insert into the response view, as the previous example shows, would fail.

When a modifiable view contains no derived columns, you can insert into it as if it were a table. However, the database server uses null as the value for any column that is not exposed by the view. If such a column does not allow nulls, an error occurs, and the insert fails.

Using the WITH CHECK OPTION Keywords

You can insert into a view a row that does not satisfy the conditions of the view; that is, a row that is not visible through the view. You can also update a row of a view so that it no longer satisfies the conditions of the view.

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. However, you can create the view 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

The preceding update by user tony is rejected as an error.

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

Because of EXISTS and other tests, which are expected to be successful when the database server retrieves existing rows, this view displays data from orders inefficiently. However, if insertions to orders are made only through this view (and you do not already use integrity constraints to constrain data), users cannot insert a back-dated order, an invalid customer number, or an excessive shipping weight and shipping charge.

Re-Execution of a Prepared Statement When the View Definition Changes

The database server uses the definition of the view that exists when you prepare a SELECT statement with that view. If the definition of a view changes after you prepare a SELECT statement on that view, the execution of the prepared statement gives incorrect results because it does not reflect the new view definition. No SQL error is generated.