Tuesday, November 13, 2007

Non-updateable views

As we know from BOL
"Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition"

But I would like to share with you one method to prevent updating view that based on single table. Let's see the following

CREATE TABLE1 (col1 int,col2 int,col3 int,col4 int,col5 int)
GO
INSERT INTO t1 VALUES (1,1,11,2,12)
INSERT INTO t1 VALUES (8,10,15,25,55)
INSERT INTO t1 VALUES (9,1,11,2,5,81)
GO
CREATE VIEW V1 WITH VIEW_METADATA
AS
SELECT
col1,
col2+0 AS col2,
col3,
col4+0 AS col4,
col5
FROM T1
GO

--Usage

UPDATE v1 SET col1=100 where col2=10
The above statement is succeed, but what happened to the next one

UPDATE v1 SET col2=1000 where col3=15
--Update or insert of view or function 'v1' failed because it contains a derived or --constant field.

You cannot derived column ,as it should be referring to the update, not the view.
In SQL Server 2000, you can handle it using an INSTEAD OF trigger.

5 comments:

haiyan said...

When I logged into the hotel, I was surprised that I had 1000 habbo credits which is unbelievable. I logged back in 5 minutes later to discover that all my habbo gold has been stolen. It gives you a virtual environment where you can use your imagination to create your own small cartoony world with your own habbo coins and interact with different people. For decorations you actually pay real monry to buy habbo gold in the range of $0.20, it may sound ridiculous. To use Hotel navigator you need to install cheap habbo credits of macromedia hockave player. There are two types of rooms, public and guest room.

Clyde said...

Thank you for your solution. I assume that I could do a similar thing with a char column by concatenating a space to the column value.

Wouldn't a security change work too? If the view has 'SELECT' authorization only then no updates could be performed. That seems easier than modifying every column in the view.

products said...

China Wholesale has been described as the world’s factory. This phenomenom is typified by the rise ofbusiness. Incredible range of products available with China Wholesalers “Low Price and High Quality” not only reaches directly to their target clients worldwide but also ensures that wholesale from china from China means margins you cannot find elsewhere and buy products wholesaleChina Wholesale will skyroket your profits.wedding dressescheap naruto cosplayanime cosplay

products said...

Women’s nike tn Shox Rivalry est le modèle féminin le plus tendance de baskets pour le sport. tn chaussuresConcernant la semelle :spyder jacketsCheap Brand Jeans Shop - True Religion Jeans cheap nike shox & Puma Shoes Online- tn nike,Diesel Jeans le caoutchouc extérieur, l’EVA intermédiaire Levis Jeanset le textile intérieur s’associent pour attribuer à la.ed hardy shirts pretty fitCharlestoncheap columbia jackets. turned a pair of double plays to do the trick.Lacoste Polo Shirts, puma basket, Burberry Polo Shirts.wholesale Lacoste polo shirts and cheap polo shirtswith great price.Thank you so much!!cheap polo shirts men'ssweate,gillette mach3 razor bladesfor men.As for Cheap Evisu JeansCheap Armani Jeanspolo shirtsPuma shoes

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!