Re: Update a Join View

See below (some people don't like top posting, although I don't really mind)

"The Flying Spontinalli" <wolf__at_tiscali.co.uk> schreef in bericht news:1160347388.663013.322020_at_i42g2000cwa.googlegroups.com...
> Surely you jest, Sir?
>
> Robbert van der Hoorn wrote:
>> "The Flying Spontinalli" <wolf__at_tiscali.co.uk> schreef in bericht
>> news:1160305021.202446.159970_at_k70g2000cwa.googlegroups.com...
>> > You can't create an instead-of trigger on an inline view.
>> >
>> > Robbert van der Hoorn wrote:
>> >> "klabu" <klabu76_at_gmail_dot_com> schreef in bericht
>> >> news:12idh3l6n195ve8_at_corp.supernews.com...
>> >> >
>> >> > 10gXE
>> >> > I get error "ORA-01776: cannot modify more than one base table
>> >> > through
>> >> > a
>> >> > join view"
>> >> > while testing the SQL below...
>> >> > Is there a way to get around this ?
>> >> >
>> >> > thanks
>> >> >
>> >> > -------------------------------------------------------------------------
>> >> > scott_at_XE> alter table emp add (dname varchar2(14));
>> >> >
>> >> > Table altered.
>> >> >
>> >> > scott_at_XE> update emp set dname = 'COKE';
>> >> >
>> >> > 14 rows updated.
>> >> >
>> >> > scott_at_XE> commit;
>> >> >
>> >> > Commit complete.
>> >> >
>> >> > scott_at_XE>
>> >> > 1 UPDATE
>> >> > 2 (SELECT me.dname medname, md.dname mddname
>> >> > 3 FROM
>> >> > 4 emp me, dept md
>> >> > 5 WHERE me.deptno = md.deptno
>> >> > AND empno = 7369 )
>> >> > 6* SET medname = 'BEER', mddname = 'WINE'
>> >> > *
>> >> > ERROR at line 6:
>> >> > ORA-01776: cannot modify more than one base table through a join
>> >> > view
>> >> >
>> >>
>> >> Klabu,
>> >>
>> >> yes you can, take a look at instead-of triggers!
>> >>
>> >> Robbert van der Hoorn
>> >> OSA it BV
>> >> The Netherlands
>> >
>>
>> You're right, but why not create an explicit view when wanting to update?
>>
>> Robbert
>

For the given example, it may look like jesting. But if updates like this occur more often in a program, one might consider to create a view where emp's are linked to dept (not using the empno of course), and create an instead of trigger. It is (in projects I have seen) not unusual to create business views for business functions where so called look-up fields are updated when base-table fields are changed. Oracle Designer for example supports this (although I must admit that in most cases these views are used to change look up fields only...)
I was expecting the case on hand was a simplified example of a more complex problem (at least I hope so) for I see no reason why one would try to create inconsistency using updates on an inline view, based on a not well normalized data model, storing department name in dept as well as emp, with different values for department name for the same department id. But again, it's only an example statement.

Robbert Received on Mon Oct 09 2006 - 05:02:44 CDT