Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: DUMB question about updatable views

Re: DUMB question about updatable views

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: Thu, 07 Aug 2003 16:52:57 +0000
Message-ID: <3213854.1060275177@dbforums.com>

Originally posted by Tk
> 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?"
> I thought the whole purpose of a view was to be 'read-only'. The
> "INSTEAD
> OF" trigger makes sense to me.
> But in this case, I did not issue an "INSTEAD OF" trigger. 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?
>
> thanks and sorry for the stupid question...

Running updates on a view is an option provided within Oracle but is rarely used in Production... i.e. in well thought out application designs. I would suggest you stay from it as a common practice and do it only as a learning exercise.

Use WITH READ ONLY keywords with create view statement to keep it read only.

CREATE OR REPLACE VIEW foo AS

   SELECT ....
   WITH READ ONLY; Regards
/Rauf Sarwar

--
Posted via http://dbforums.com
Received on Thu Aug 07 2003 - 11:52:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US