Re: DUMB question about updatable views

"tk" <theronk_at_charter.net> wrote in message news:<vj4lbu6rmav585_at_corp.supernews.com>...
> Hi Folks:
>
> Going thru the Oracle 8i book (even though I installed 9i), I'm just playing
> around with Triggers/Views to familiarize myself more with them...
>
> I think the "INSTEAD OF" is pretty darn slick...
>
> I am slightly confused:
> I created a view:
>
> CREATE OR REPLACE FORCE VIEW TKOUSEK.STUDENTS_VIEW
> (FIRST_NAME, LAST_NAME, ID)
> AS
> SELECT FIRST_NAME, LAST_NAME, ID FROM STUDENTS WHERE ID = 10005;
>
> and then played around by issuing the following update:
> update students_view set first_name = 'TEST';
>
> I thought I'd get an Oracle error to the tune of "Cannot invoke the update"
> on a view but
> instead, it performed the update....
>
>
> So then I played around further and modified the view to join to another
> table
>
>
> CREATE OR REPLACE FORCE VIEW TKOUSEK.STUDENTS_VIEW
> (FIRST_NAME, LAST_NAME, ID, TOTAL_CREDITS)
> AS
> SELECT FIRST_NAME, LAST_NAME, ID, a.TOTAL_CREDITS FROM STUDENTS, MAJOR_STATS
> a
> where STUDENTS.MAJOR = 'Computer Science' and STUDENTS.MAJOR = a.MAJOR;
>
> and called the update
>
> UPDATE STUDENTS_VIEW SET TOTAL_CREDITS = 21;
>
> and got the following:
> SQL> UPDATE STUDENTS_VIEW SET TOTAL_CREDITS = 21;
> UPDATE STUDENTS_VIEW SET TOTAL_CREDITS = 21
> *
> ERROR at line 1:
> ORA-01779: cannot modify a column which maps to a non key-preserved table
>
> ----------
>
> So I guess my question is: "What are the rules governing the user to issue
> update statements on a view?"

It depends. Just as the error message hints, there are conditions to being able to Insert/Update/Delete rows in a VIEW.

> I thought the whole purpose of a view was to be 'read-only'.

Open your mind to the real world.

> The "INSTEAD
> OF" trigger makes sense to me.

They allow you to program the cases where the VIEW cannot support DML operations.

> But in this case, I did not issue an "INSTEAD OF" trigger.

Yes, that's the point: there are views which can be modified without special programming to give it correct meaning/operation.

> Are you allowed
> to perform updates on views that
> join to other tables or are they restricted to views that gather all of
> their data from just one table?

It depends on how the VIEW is formed and how the UPDATE is expressed. there is a brief description of this in the SQL standard. (don't have my ORACLE manuals handy). Essentially, if the relational algrebra needed to locate the correct rows from the base (or "simply underlying") tables can be performed by the DB, then the view is updatable.

>
> thanks and sorry for the stupid question...

Which one was supposed to be the stupid question?

Actually, IMO, there are no stupid questions, only stupid answers.

HTH
  ed Received on Thu Aug 07 2003 - 14:54:30 CDT