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.
Tuesday, November 13, 2007
Subscribe to:
Post Comments (Atom)
1 comment:
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.
Post a Comment