Non - Updatable View

Question

I have a view which is read only, and understand that views are not updateable with group by and distinct clauses. Does that also apply to expressions in a view ?

For example, I need to have an expression in a view. Of course I cannot update an expression column since it is derived, but I am trying to update just a column (fkCPRId) in the base table (dbo.Pager) of the following view, and I am unable to do so. I get an error message:

No Row was updated.

Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (3 rows). Correct the errors and retry or press ESC to cancel the changes.

ALTER VIEW [dbo].[vwMsgMobileDevicePersonnel]
  AS 
  
  
  SELECT P.[id]
      ,P.[fkCprId]
      ,P.[ts_issue]
      ,P.[title]
      ,P.[phone]
      ,P.[email]
      ,P.[status]
      ,P.[pagerDeviceId]
      ,P.[pager_model]
      ,P.[pager_cap_code]
      ,P.[pager_serial]
      ,P.[pager_number]
      ,'('+substring(P.[pager_number],1,3)+') '+ substring(P.[pager_number],4,3)+'-'+substring(P.[pager_number],7,4) as pager_number_display
      ,P.[pager_status]
      ,P.[pager_cat]
      ,P.[pager_vendor]
      ,P.[pager_owner_type]
      ,P.[comment]
      ,P.[dhCprId]
      ,P.[dh_name]
      ,P.[dh_phone]
      ,P.[dh_email]
      ,P.[LastUpdatedDate]
      ,P.[CurrentCPRId]
      ,P.[pkPersonnelType]
      ,P.[pkPersonnelType]+convert(varchar(8),P.fkcprid) as UniquePersonnelId
      ,Isnull(P.lastname,'')+ ', '+Isnull(P.firstname,'')+' '+Isnull( P.middlename,'')+ ' '+isnull(P.[rank],'') AS ManualFullName 
      ,Case when P.pkPersonnelType='C' then Isnull(C.lastname,'')+ ', '+Isnull(C.firstname,'')+' '+Isnull( C.middlename,'')+ ' '+isnull(C.[rank],'') when P.pkPersonnelType='X' then 
       Isnull(X.lastname,'')+ ', '+Isnull(X.firstname,'')+' '+Isnull( X.middlename,'')+ ' ' else '' end AS FullName
      ,Case when P.pkPersonnelType='C' then c.Director When P.pkPersonnelType='X' then '<<Exception>>' end as Director
      ,Case when P.pkPersonnelType='C' then C.Department When P.pkPersonnelType='X' then '<<Exception>>' end as Department
    
      ,Case when P.pkPersonnelType='C' then C.EmailAddress When P.pkPersonnelType='X' then X.Email end as EmailAddress
      ,Case when P.pkPersonnelType='C' then C.WorkPhone When P.pkPersonnelType='X' then X.OfficePhone end as WorkPhone
    
      ,Assigned=Case when P.fkCPRId=0 then 1 else 0 end
  FROM [dbo].[pager] P
  left JOIN dbo.CPR C 
  ON pkPersonnel=[fkCprId]
  Left Join dbo.Exception X on
  X.ExceptionPKPersonnelId=P.fkCPRId
  where p.pager_status='active'
  
 

---

John

Answer 1

Any view can be made updatable with INSTEAD OF triggers and custom logic.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 2

I have a view which is read only, and understand that views are not updatable with group by and distinct clauses. Does that also apply to expressions in a view ?

For example, I need to have an expression in a view. Of course I cannot update an expression column since it is derived, but I am trying to update just a column (fk_cpr_id) in the base table (dbo.Pager)[only one pager? That is what you said] of the following view, and I am unable to do so. I get an error message:

No Row was updated.

Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (3 rows). Correct the errors and retry or press ESC to cancel the changes. 

That is a clear error message. Your keys are screwed up! Since we have no DDL, nobody can help you. What little you did post is awful. 

Your code is full of “tibbling”; this is a programming error in which you put meta data affixes on data element names. The “vw_” is called “Volkswagen programming” and we really laugh at that one. Your “fkcpr_id” is a violation of ISO-11179 and basic data modeling; the “cpr_id” is used as a foreign key here, but that is not what it is by its nature. 

The rest of the data element names are too generic or improperly designed to be valid. 

Camel-case adds 8-12% time to debugging code, which is why we do not use it any more. There is no such thing as a magical generic “id” (really awful noobs will use IDENTITY here!). We do not do display formatting in the database, so all of that messing with phone numbers should go. You missed COALESCE() and used ISNULL(), etc.  

Because I worked with punch card in the 1960's, I remember those leading commas at the front of the card. We did that so we could shuffle the cards or re-use them.  Never use them today, of course. It makes the code unreadable and a bitch to maintain. 

You need more help than you can get in a forum. Even an INSTEAD OF trigger will not help; you have to maintain data integrity in the base table, Pagers. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL