Updating a View

[amitkannan]

We Create a view by using 2 tables for( ex.emp table and dept table) after that i changed some rows in the emp table what will happen in the view? whether the view also gets updated or not?

[bhaski]

I prepared this document from oracle documentation think may help u--

a view cannot be modified by update, insert, or delete statements if the view query contains any
of the following constructs:
1.a set operator
2.a distinct operator
3.an aggregate or analytic function
4.a group by, order by, model, connect by, or start with clause
5.a collection expression in a select list
6.a subquery in a select list
7.a subquery designated with read only
8.joins, with some exceptions as ---

general rule--> any insert, update, or delete operation on a join view can modify only one underlying
base table at a time.

Update rule --> all updatable columns of a join view must map to columns of a key-preserved table.
If the view is defined with the with check option clause, then all join columns and all columns of repeated tables are not updatable.

Delete rule--> rows from a join view can be deleted as long as there is exactly one key-preserved table
in the join. The key preserved table can be repeated in the from clause. If the view is defined with the with check option clause and the key preserved table is repeated, then the rows cannot be deleted from the view.

Insert rule--> an insert statement must not explicitly or implicitly refer to the columns of a non-key-preserved table. If the join view is defined with the with check option clause, insert statements are not permitted.

Now question is what is key preserved table
a table is said to be key preserved if every key of the table is a key of the join of the table

[raj_imr2000]

Hi Amit,

After creating the view if you are changing data into tables then the view will also gets updated.

Thanks
Raj

[susarlasireesha]

suppose u create a view using empno,name,sal,dept.deptno,dept.loc from emp,dept tables using joining condition( i.e emp.deptno=dept.deptno )when
u insert details in emp table , view will be updated if the deptno is already in dept table if deptno not in dept table then view will not be updated.

[chaitueeturi]

generally a view will be stored as a statament in database.
If u want retrieve data using this view,then it replaces the view with a select statement and it will be executed on the tables on which u created the view.I think,from this u can come to know that the data retrieved is from the updated tables only.

[priyasp_msit]

We Create a view by using 2 tables for( ex.emp table and dept table) after that i changed some rows in the emp table what will happen in the view? whether the view also gets updated or not?

hi
u have using composite view,this does not insert or update or delete for sql but u insert or update or delete in sql only after create instead of trigger for plsql
.