 |
» |
|
|
|
INSERT, UPDATE and DELETE operations may be performed through views
or as qualified by search conditions
provided the views or search conditions are based on updatable queries.
UPDATE WHERE CURRENT and DELETE WHERE CURRENT operations may be
performed through cursors provided the cursors are based on updatable
queries.
Queries that underlie views and cursors are called updatable queries when
they conform to all of the following updatability criteria: No DISTINCT, GROUP BY, or HAVING clause is specified in the outermost
SELECT statement; and no aggregate is specified in the outermost select
list.
The FROM clause specifies exactly one table, either directly or
through a view. If the FROM clause specifies a view,
the view must be based on an updatable query.
For INSERT and UPDATE through views, the select list in the view
definition must not contain any arithmetic expressions.
It must contain only column names.
For UPDATE WHERE CURRENT and DELETE WHERE CURRENT operating on cursors,
the cursor declaration must not include an ORDER BY clause, and the
query expression must not contain subqueries, the UNION or UNION ALL statement, or any
nonupdatable views.
The target table of an INSERT, UPDATE, or DELETE operation is the base
table to which the changes are actually being made.
For noncursor INSERT, UPDATE, or DELETE operations, the view
definition must not include any
subqueries which contain the target table in their FROM clause; and
if a search condition is given, it must not include any subqueries
which contain the target table in their FROM clause.
If a query is updatable by the previous rules, then the
underlying table is an updatable table. Otherwise it is
considered a read-only table and is locked accordingly. This
means that in cursor operations, SIX,
IX, and X locks are not used unless the query that underlies the
cursor matches the updatability criteria and was declared with columns
for UPDATE. In noncursor view operations, SIX, IX, and X locks are not
obtained unless the table underlying the view is updatable. Refer
to the chapter "Concurrency Control through Locks and Isolation Levels"
for a complete explanation of SIX, IX, and X locks.
|
