Re: updating tables from views

gmohata_at_yahoo.com (Girish Mohata) wrote in message news:<42a840c2.0109170040.2aa0a4db_at_posting.google.com>...
> Hi,
> I have created a view:
>
> Create View sturec as (
> Select sid,slname,grade,assessnumber
> from student,enrolment,assessment
> where student.sid=enrolment.sid
> and enrollment.cid=assessment.cid);
>
> Now when i insert a record into this view using SQL:
>
> insert into sturec values ....
>
> or using Oracle Forms 6.0,
>
> how can i effect the inserts into the base tables from the views
> (after authentication and validation ofcourse).
>
> Kind Regards,
> Girish

Girish, I have posted the SQL to allow you to see which columns of a view are updatable, if any. You need to look-up Oracle's rules on updating via views. Basically you can only update one table via a view and then only if the result of the view is a key preserved table.  This basically means that the result set rows must correspond on a one for one basis to rows in the target table and these rows can be identified by a unique key.

I cannot remember the limitations right now but it may be worth your time to also look-up 'instead of triggers' which can be placed on views.

set echo off
rem
rem SQL*Plus script to display update DML status of view columns rem
rem 19990413 Mark D Powell New Script, ver. 8 rem
set pagesize 55
set verify off
column owner format a12

accept view_nm prompt 'Enter the view name to be checked: ' prompt This query will return "no rows" if entered value is not a view

select uc.owner,

       uc.column_name, uc.updatable, uc.insertable, uc.deletable from all_updatable_columns uc,

       all_views v

where  uc.table_name = upper('&view_nm')
and    uc.table_name = v.view_name
and    uc.owner      = v.owner

• Mark D Powell --