Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    view with read only option ?

    Hi,

    Is there anyway to create view with read only option as in Oracle ? (OR) is there any
    workaround for the same ?

    Please advice,

    Thanks,
    Sam

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,971
    What exactly do you mean by "read only" in your question?

    If you want the users to be able to take data out of the database, but not be able to put data into the database, then yes you can do that. Make each column definition an rvalue expression instead of an lvalue expression. The exact mechanics for doing this vary depending on column datatype and from one version of Oracle to another, but the process is usually pretty simple.

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    Yes read only option, you should not be able put the data inside, but you should be able to fetch the data. But you have given solution in Oracle. Is it a typo-error ? is this a solution in SQL Server. I will make it clear.

    The below is an Oracle query :

    create view view1 as select col1 from test with read only;

    Please give the equivalent for the above in SQL Server.

    Thanks,
    Sam

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,971
    Sorry, I misunderstood what you wanted.

    SQL Server doesn't support read only views the same way the Oracle does. In fact, it is much more complex to create a read only view in SQL Server.

    The simplest way to change a view to prevent it from being updatable is to artificially mangle one of the key columns from the underlying table. For example:
    Code:
    SELECT
       CASE WHEN CustomerID IS NULL THEN NULL ELSE CustomerID END AS CustomerID
    ,  CompanyName, ContactName, Phone
       FROM Northwind.dbo.Customers
    The view looks and feels normal in every way, except it can't be updated because the key column is an expression.

    -PatP

Posting Permissions

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