Re: UPDATE or INSERT on a view ?

"Zixiong WANG" <z.wang_at_sysium.com> wrote

> Can I execute an UPDATE or an INSERT to a view ?

Why not read up on the CREATE VIEW statement in the Oracle SQL Reference Manual?
(http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_85a.htm#2065512)

Here's a snippet:

--
An updatable view is one you can use to insert, update, or delete base
table rows. You can create a view to be inherently updatable, or you
can create an INSTEAD OF trigger on any view to make it updatable.

To learn whether and in what ways the columns of an inherently
updatable view can be modified, query the USER_UPDATABLE_COLUMNS data
dictionary view. (The information displayed by this view is meaningful
only for inherently updatable views.)

    * If you want the view to be inherently updatable, it must not
contain any of the following constructs:
          o A set operator
          o A DISTINCT operator
          o An aggregate or analytic function
          o A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause
          o A collection expression in a SELECT list
          o A subquery in a SELECT list
          o Joins (with some exceptions as described in the paragraphs
that follow).
    * In addition, if an inherently updatable view contains
pseudocolumns or expressions, you cannot update base table rows with
an UPDATE statement that refers to any of these pseudocolumns or
expressions.
    * If you want a join view to be updatable, all of the following
conditions must be true:
          o The DML statement must affect only one table underlying
the join.
          o For an INSERT statement, the view must not be created WITH
CHECK OPTION, and all columns into which values are inserted must come
from a key-preserved table. A key-preserved table in one for which
every primary key or unique key value in the base table is also unique
in the join view.
          o For an UPDATE statement, all columns updated must be
extracted from a key-preserved table. If the view was created WITH
CHECK OPTION, join columns and columns taken from tables that are
referenced more than once in the view must be shielded from UPDATE.
    * For a DELETE statement, if the join results in more than one
key-preserved table, then Oracle deletes from the first table named in
the FROM clause, whether or not the view was created WITH CHECK
OPTION.

--
Billy