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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 27 Feb 2002 06:45:47 -0800
Message-ID: <a5irer01lm8@drn.newsguy.com>

In article <a5i526$nqu$1_at_ns1.fe.internet.bosch.com>, "Stefan says...
>
>
>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?
>

well, the message is pretty clear on this one -- you cannot modify more then one base table through a join view:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t1 ( x int primary key, y int );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t2 ( a int primary key, b int );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace view v

  2 as
  3 select * from t1, t2 where t1.x = t2.a;

View created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into v values ( 1, 1, 2, 2 );

insert into v values ( 1, 1, 2, 2 )
*
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view

Ok, so what can you do here? We can teach the database how to update this view safely with instead of triggers. Consider:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace trigger v_io_iud   2 instead of insert or update or delete on v   3 begin

  4          if ( inserting )
  5          then
  6                  insert into t1 values ( :new.x, :new.y );
  7                  insert into t2 values ( :new.a, :new.b );
  8          elsif ( updating )
  9          then
 10                  update t1 set y = :new.y, x = :new.x where x = :old.x;
 11                  update t2 set b = :new.b, a = :new.a where a = :old.a;
 12          else
 13                  delete from t1 where x = :old.x;
 14                  delete from t2 where a = :old.a;
 15          end if;

 16 end;
 17 /

Trigger created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into v select rownum, rownum, rownum, -rownum from all_objects where rownum <= 5;

5 rows created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from v;

         X Y A B

---------- ---------- ---------- ----------
         1          1          1         -1
         2          2          2         -2
         3          3          3         -3
         4          4          4         -4
         5          5          5         -5

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> update v set y = y*2, b = b*3 where x = 5;

1 row updated.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from v;

         X Y A B

---------- ---------- ---------- ----------
         1          1          1         -1
         2          2          2         -2
         3          3          3         -3
         4          4          4         -4
         5         10          5        -15

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> delete from v;

5 rows deleted.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from v;

no rows selected

>Thanx for tips!
>
>Stefan D.
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Wed Feb 27 2002 - 08:45:47 CST