Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Unanswered: Differences between views and tables?

    Hi,

    I need to know the differences between views and tables? Also can be update/insert/delete data from views?

    Thanx,
    Sams

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,
    the BIG diff between tables and views is hat u can store data in a table, not in a view. A view is only representation of data from different sources. It's like a predefined query, which is stored in the database.
    u can use a view to show data to a user without having the user to tell in what tables the data is.
    Data in a view cannot be inserted updated or deleted, basicly.
    But when needed for oracle forms or other purposes, special triggers can be defined on the views, to hanlde updates, insertes or deletes (INSTEAD OF triggers.)

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Oct 2003
    Posts
    87
    Don't know for sure in Oracle, but in most RDBMs, if a view is based on a single table (and other conditions are met) you can delete, insert, and update the "base table" via the view.
    Oracle - DB2 - MS Access -

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    in oracle u cannot insert, update or delete data from a view directly.
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Oct 2003
    Location
    Republic of Srpska, Bosnia and Herzegovina
    Posts
    35
    yes, you can do it


    SQL> create table proba2(a integer, b varchar2(30));

    Table created.

    SQL> create view tempview as select * from proba2;

    View created.

    SQL> insert into tempview(a,b) values(1,'yes it can');

    1 row created.

  6. #6
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Talking Re: Differences between views and tables?

    Hi,

    TABLE is the source on which VIEW is Created. If the underlying TABLE is removed then VIEW doesn't works. VIEW is created for DATA SECURITY Purpose. You can have any Number of views on a TABLE. We can INSERT/UPDATE/DELETE records on the view. But DML operations can be done only on the columns & rows selected by the view. They will affect the Original records on the Source table.
    SATHISH .

  7. #7
    Join Date
    Oct 2003
    Posts
    22

    Re: Differences between views and tables?

    there r 2 types of views - (1)normal view (2) materializee view.

    DMLs can be performed on (2)

    DMLs on (1) - on certain conditions only. [ refer online documentation ]

    if simple view - refering only single table
    containd all not null columns
    does not contain functional columns
    does not contain group by or order by clause
    etc.

    be caereful while insertin through views.

    for deleting and updating - best is to use INSTED OF trigger
    which should be used in inserting also ( under certain conditions. )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •