| 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: Updatable Views
Comments embedded.
On Sep 21, 11:45 am, DJH <NOS..._at_NOSPAM.COM> wrote:
> Are there are traps to know of when using Updateable Views in Oracle?
>
Yes.
> ex.
> table a, table b
> upd view: select a.foo, b.bar from a,b where a.pk=b.pk;
This, most likely, is not an updateable view. I imagine that foo is not unique to a, nor is it unique in the result set of the join. Thus you would be attempting to update a non-key-preserved table and an ORA-01779 would result:
SQL> create or replace view emp_dname
2 as
3 select empno, ename, emp.deptno, dname, loc
4 from emp, dept
5 where dept.deptno = emp.deptno
6 /
View created.
SQL> update emp_dname
2 set dname = 'REGION SALES'
3 where deptno = (select deptno from dept where dname = 'SALES')
4 /
set dname = 'REGION SALES'
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved
table
SQL> In this case dname is not unique in the join, therefore DEPT is not a key-preserved table. Contrary to that EMP is a key-preserved table since EMPNO retains it's uniqueness and is a key of both the source table (EMP) and the resulting join.
>
> I am assuming that Oracle will take care to update the right rows in
> either table as appropriate.
Presuming you have an updateable view, yes.
> Also will the triggers fire for each table
> as a normal table update?
>
SQL> create or replace view emp_dname
2 as
3 select e.empno, e.ename, e.sal, e.deptno, d.dname, d.loc
4 from emp e, dept d
5 where d.deptno = e.deptno;
View created.
SQL> create or replace trigger emp_test
2 before insert or update on emp
3 for each row
4 begin
5 dbms_output.put_line('Yikes!!! DML!!!!!');
6 end;
7 /
Trigger created.
SQL> select *
2 from emp_dname;
EMPNO ENAME SAL DEPTNO DNAME LOC ---------- ---------- ---------- ---------- --------------
7782 CLARK 2450 10 ACCOUNTING NEW YORK
7839 KING 5000 10 ACCOUNTING NEW YORK
7934 MILLER 1300 10 ACCOUNTING NEW YORK
7566 JONES 2975 20 RESEARCH DALLAS
7902 FORD 3000 20 RESEARCH DALLAS
7876 ADAMS 1100 20 RESEARCH DALLAS
7369 SMITH 800 20 RESEARCH DALLAS
7788 SCOTT 3000 20 RESEARCH DALLAS
7521 WARD 1250 30 SALES CHICAGO
7844 TURNER 1500 30 SALES CHICAGO
7499 ALLEN 1600 30 SALES CHICAGO
EMPNO ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- --------------
7900 JAMES 950 30 SALES CHICAGO
7698 BLAKE 2850 30 SALES CHICAGO
7654 MARTIN 1250 30 SALES CHICAGO
14 rows selected.
SQL> update emp_dname
2 set sal = sal+100
3 where dname = 'SALES'
4 /
Yikes!!! DML!!!!! Yikes!!! DML!!!!! Yikes!!! DML!!!!! Yikes!!! DML!!!!! Yikes!!! DML!!!!! Yikes!!! DML!!!!!
6 rows updated.
SQL> select *
2 from emp_dname;
EMPNO ENAME SAL DEPTNO DNAME LOC ---------- ---------- ---------- ---------- --------------
7782 CLARK 2450 10 ACCOUNTING NEW YORK
7839 KING 5000 10 ACCOUNTING NEW YORK
7934 MILLER 1300 10 ACCOUNTING NEW YORK
7566 JONES 2975 20 RESEARCH DALLAS
7902 FORD 3000 20 RESEARCH DALLAS
7876 ADAMS 1100 20 RESEARCH DALLAS
7369 SMITH 800 20 RESEARCH DALLAS
7788 SCOTT 3000 20 RESEARCH DALLAS
7521 WARD 1350 30 SALES CHICAGO
7844 TURNER 1600 30 SALES CHICAGO
7499 ALLEN 1700 30 SALES CHICAGO
EMPNO ENAME SAL DEPTNO DNAME LOC
---------- ---------- ---------- ---------- --------------
7900 JAMES 1050 30 SALES CHICAGO
7698 BLAKE 2950 30 SALES CHICAGO
7654 MARTIN 1350 30 SALES CHICAGO
14 rows selected.
SQL>
> An updateable view will be come very handy for me when creating some
> forms I am about to create. Are there are drawbacks to using them?
>
Yes and I've mentioned them throughout the response.
> Thanks
David Fitzjarrell Received on Fri Sep 21 2007 - 13:51:37 CDT
 |
 |