 |
» |
|
|
|
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 Chapter 5 “Concurrency Control
through Locks and Isolation Levels” for a complete explanation
of SIX, IX, and X locks. |
