Re: cannot modify more than one base table through a join view

"Stefan Donath" <harald.gogger_at_de.bosch.com> wrote in message news:<a5i526$nqu$1_at_ns1.fe.internet.bosch.com>...
> Hi there!
>
> I created a view that bases on two tables.
> These tables connected at each other through a 1:1-reference
> In the second table there is a primary key with a trigger that simulates a
> AutoCount-Field.
> In the first table there is a unique-indexed foreign key.
>
> If I want to insert data (or change data in the second table) it gives the
> message "cannot modify more than one base table through a join view".
> Is there a fault in my query/view, or in which way you solve this problem?
>
> Thanx for tips!
>
> Stefan D.

Stefan, the rule is that only one table is elgible to be updated via the join view on the key preserved table. It might be possible write a view where both tables meet the key preserved rule but you are only going to be able to update one of them via native DML. You can use the following script to check which columns of a view are updatable:

set echo off
rem
rem SQL*Plus script to display update DML status of view columns rem
rem 19990413 Mark D Powell New Script, ver. 8 rem
set pagesize 55
set verify off
column owner format a12

accept view_nm prompt 'Enter the view name to be checked: ' prompt This query will return "no rows" if entered value is not a view

select uc.owner,

       uc.column_name, uc.updatable, uc.insertable, uc.deletable from all_updatable_columns uc,

       all_views v

where  uc.table_name = upper('&view_nm')
and    uc.table_name = v.view_name
and    uc.owner      = v.owner

You might consider if an instead-of trigger could be placed on the view to perform the update action. I haven't used them much so all I can do is point you to the manual to see if they will allow you to do what you want.

• Mark D Powell --