Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006

    Unanswered: Views vs Stored Procedures for updating etc..

    I have a number of relational tables in my schema.
    I am developing a front-end for a DB and I would like to create a VIEW as a virtual table which maps directly to an application form which is used to capture the data.
    This view would follow the form step-by-step so that inserting would be really easy and I would not have to join tables and select each column of info from each table that I needed to capture data with, instead just work with one virtual table that I can use one simple "insert into table" statement. It would in essence reflect the application form.

    Q1: Can updating and inserting be done on VIEWS and is this a good idea?
    Q2: Are stored procedures a better alternative?
    Q3: I see that a lot of posts say that VIEWS are used to moderate security. How exactly does it achieve this.
    Last edited by grooverinthesouth; 04-06-06 at 11:38.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    A1: Depending on your database engine and view construction, it may be possible to update a view. This makes no more sense to me than updating a SELECT.
    A2: Stored procedures are a better alternative in my opinion.
    A3: Views provide security in many ways. They allow the base tables to be left secured (no permissions need to be granted on the base tables). They allow the administrator to restrict which columns of which tables the user can see. Views also allow the developer/administrator to "bury" complex logic needed to get correct answers, while still making that logic easily accessible to end users.


  3. #3
    Join Date
    Sep 2004
    Views on joins of tables are never updatable.

    An other alternative (in combination with a view) could be the use of triggers.
    It really depends on the situation at hand which implementation is better.

    Not only does a view allow resticting access to certain columns of a table, but also to certain rows of a table. Or even (when using GROUP BY in a view definition) to only summary reports of a table.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting

  4. #4
    Join Date
    Sep 2002
    Provided Answers: 1
    Quote Originally Posted by Peter.Vanroose
    Views on joins of tables are never updatable.
    They are in some DBMSs. For example, Oracle allows the update of columns from "key-preserved" tables in join views. This means that you can update columns from a table whose primary key could also serve as a primary key for the view.

Posting Permissions

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