If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > view with read only option ?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,781
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,781
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On