Results 1 to 6 of 6

Thread: Updating a View

  1. #1
    Junior Member
    Join Date
    Jan 2008
    Answers
    3

    Updating a View

    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?


  2. #2
    Contributing Member
    Join Date
    Apr 2006
    Answers
    46

    Re: Updating a View

    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

    Last edited by bhaski; 01-25-2008 at 06:38 AM.

  3. #3
    Junior Member
    Join Date
    Jan 2008
    Answers
    2

    Re: Updating a View

    Hi Amit,

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

    Thanks
    Raj


  4. #4
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Updating a View

    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.


  5. #5
    Junior Member
    Join Date
    Jan 2008
    Answers
    24

    Re: Updating a View

    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.


  6. #6
    Contributing Member
    Join Date
    Jul 2007
    Answers
    42

    Re: Updating a View

    Quote Originally Posted by amitkannan View Post
    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
    .


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact