Data manipulation through logical views

1.	You cannot INSERT, UPDATE or DELETE data through a Logical View if its SQL Statement contains one of the following:

2.	You can INSERT, UPDATE or DELETE data in a table T through a Logical View V only if the SELECT list in V's SQL Statement names ALL the primary key columns of T.

3.	You cannot INSERT or UPDATE a view column that is subject to any SQL function in the view's SELECT list.

4.	You cannot INSERT data through a Logical View that does not offer access to mandatory columns, unless those columns have default values.

The inability to perform data manipulation may take different shapes. An error message may appear. Inability to UPDATE may be signalled by non-updatable fields. Inability to DELETE may be tacit: if the Logical View joins two tables and one does not have all primary key columns named in the view's SQL, then DELETE may be performed only on the other table, without the user being signalled in any way about

If the primary key column(s) of more than one table appear in the SELECT list, manipulations (inserts, updates and deletes) are executed on each of these tables.

If your Logical View does not offer the data manipulation options you want, you can create these options by turning it into a rule-based logical view.

In this Logical View, only the EMPLOYEE table has a primary key in the SELECT list. Therefore only the EMPLOYEE table is updatable through this Logical View:

SELECT     d.name
,          e.id
,          e.dept_id
,          e.name
FROM       department d
,          employee e
WHERE      e.dept_id = d.id

In this Logical View, both underlying tables has a primary key in the SELECT list. A manipulation on this Logical View will be executed on both tables:

SELECT     d.id
,          d.name
,          e.id
,          e.dept_id
,          e.name
FROM       department d
,          employee e
WHERE      e.dept_id = d.id

In this Logical View, you can UPDATE the FAMILY_NAME column but not the SALUTATION column, because the SALUTATION column is subject to SQL functions:

SELECT     p.person_id
,          p.family_name
,          'Dear ' ||
           DECODE(
              p.gender
           ,  'M'
           ,  'Mr. '
           ,  'Ms. '
           ) ||
           UPPER( SUBSTR( p.family_name, 1, 1 ) ) ||
           LOWER( SUBSTR( p.family_name, 2 ) ||
           ': '
           ) salutation
FROM       person p