Prevent field updates from a view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Prevent field updates from a view

Author

Topic

obiron
Starting Member

23 Posts

Posted - 2009-03-03 : 08:20:16

Hi guys,

I have a view which shows some data from a table, filtered to show records that meet some business rules. I want to prevent the user from updating any of the fields except the boolean flag that will then exclude the record from further processing.

Can this be done in MSSM 2005?


SELECT     
           OP_UniqueID AS Appt_ID, 
           HospitalUnitNumber, 
           NHSNumber, 
           Forename, 
           Surname, 
           bExclude_from_SUS AS [Exclude From SUS]
FROM      
           dbo.tbl_OP_Patient AS P
WHERE     
          (Forename LIKE '%dummy%') 
  OR      (Forename LIKE '%test%') 
  OR      (Forename LIKE '%mouse%') 
  OR      (Surname LIKE '%DUMMY%') 
  OR      (Surname LIKE '%test%') 
  OR      (Surname LIKE '%mouse%')
  AND (OP_UniqueID IN
        (
          SELECT     OP_UniqueID
          FROM          dbo.tbl_Con_OP_dataload
         )
       )

I only want to be able to update bExclude_from_SUS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 08:55:10

sounds like what you need is to create view with check option

http://msdn.microsoft.com/en-us/library/ms187956.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 09:00:06

Easier way...

create table test 
(
surname varchar(20),
age int
)
go
create view view1
as
select '' + surname as surname, age from test
go
insert test select 'Peso', 40
select * from view1
go

update view1 set surname = 'Saint'	-- fails with "Update or insert of view or function 'view1' failed because it contains a derived or constant field."
update view1 set age= 39		-- works
go
select * from view1
go
drop table Test
drop view view1

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources