| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Creating Views
Kenneth C Stahl wrote:
>
> Connor McDonald wrote:
>
> > You can even do mods to joins in 7.3 - as long as they are
> > key-preserved.
>
> Can you show an example?
From the doco:
Updatable Join Views
A join view is defined as a view that has more than one table or view in
its FROM clause (a join) and that does not use any of these clauses:
DISTINCT, AGGREGATION, GROUP BY, START WITH, CONNECT BY, ROWNUM, and set
operations (UNION ALL, INTERSECT, and so on).
An updatable join view is a join view, which involves two or more base tables or views, where UPDATE, INSERT, and DELETE operations are permitted. The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS, and USER_UPDATABLE_COLUMNS contain information that indicates which of the view columns are updatable.
Table 10-1 lists rules for updatable join views.
Table 10-1 Rules for INSERT, UPDATE, and DELETE on Join Views
Rule Description
General Rule
Any INSERT, UPDATE, or DELETE operation on a join view can modify only
one underlying base table at a time.
UPDATE Rule
All updatable columns of a join view must map to columns of a key
preserved table. If the view is defined with the WITH CHECK OPTION
clause, then all join columns and all columns of repeated tables are
non-updatable.
DELETE Rule
Rows from a join view can be deleted as long as there is exactly one
key-preserved table in the join. If the view is defined with the WITH
CHECK OPTION clause and the key preserved table is repeated, then the
rows cannot be deleted from the view.
INSERT Rule
An INSERT statement must not explicitly or implicitly refer to the
columns of a non-key preserved table. If the join view is defined with
the WITH CHECK OPTION clause, INSERT statements are not permitted.
Using the good ol' scott schema, imagine a view joining EMP and DEPT on DEPTNO. Then you could update the columns that relate to the EMP table, since the rows in the view have a 1 to 1 mapping to rows in EMP.
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Wed Aug 25 1999 - 01:17:28 CDT
 |
 |