Update or insert of view or function failed because it contains a derived or constant field

Question

I have been trying to understand what is wrong with the following view, and unfortunately I was not able to find my answer anywhere, other than using triggers, which I would like to avoid. Given the following view, when I try to insert into it I get the error above, however if I remove the inner join to the Company table everything seems to work just fine:

CREATE VIEW [dbo].[vwCheckBookingToCheck]
WITH SCHEMABINDING
AS
SELECT  [checkUser].[CheckID] ,
        [checkUser].[CheckToTypeID] ,
        [checkUser].[CheckNumber] ,
        [checkUser].[CheckDate] ,
        [checkUser].[CheckAmount] ,
        [checkUser].[CheckStatusID] ,
        [checkUser].[CheckAcceptedBy] ,
        [checkUser].[CreatedBy] ,
        [checkUser].[CreatedDateTime] ,
        [checkUser].[CheckToUserID] [ToID],
        [checkUser].[CheckFromCompanyID] [FromID],
        [companyFrom].[CompanyName]
FROM    [dbo].[CheckUser] [checkUser]
        INNER JOIN [dbo].[Company] [companyFrom] ON [companyFrom].[CompanyID] = [checkUser].[CheckFromCompanyID]

UNION ALL

SELECT  [checkCompany].[CheckID] ,
        [checkCompany].[CheckToTypeID] ,
        [checkCompany].[CheckNumber] ,
        [checkCompany].[CheckDate] ,
        [checkCompany].[CheckAmount] ,
        [checkCompany].[CheckStatusID] ,
        [checkCompany].[CheckAcceptedBy] ,
        [checkCompany].[CreatedBy] ,
        [checkCompany].[CreatedDateTime] ,
        [checkCompany].[CheckToCompanyID] [ToID],
        [checkCompany].[CheckFromCompanyID] [FromID] ,
        [companyFrom].[CompanyName]
FROM    [dbo].[CheckCompany] [checkCompany]
       INNER JOIN [dbo].[Company] [companyFrom] ON [companyFrom].[CompanyID] = [checkCompany].[CheckFromCompanyID]

GO

Here is my insert, I am only inserting in [CheckUser] or [CheckCompany]:

INSERT INTO [dbo].[vwCheckBookingToCheck]   
    ( [CheckToTypeID] ,
      [CheckNumber] ,
      [CheckDate] ,
      [CheckAmount] ,
      [CheckStatusID] ,
      [CheckAcceptedBy] ,
      [CreatedBy] ,
      [CreatedDateTime] ,
      [ToID] ,
      [FromID] 
    )
     SELECT 2,
      'Test' , -- CheckNumber - varchar(255)
      '2014-08-23 20:07:42' , -- CheckDate - date
      1233 , -- CheckAmount - money
      0 , -- CheckStatusID - int
      1 , -- CheckAcceptedBy - int
      1 , -- CreatedBy - int
      '2014-08-23 20:07:42' , -- CreatedDateTime - datetime
      1,  -- ToID - int
      1  -- FromID - int

CheckToTypeID is my check constraint, is there any way to make this view work with inner joins? Again, if I remove the inner joins I am able to get it to work, but I would like to keep them if possible.

I am using SQL Server 2012, any help is appreciated.

Thanks, Paul

Answer 1

This is a bit long for a comment.

I cannot readily find the 2012 documentation on this subject, but the SQL Server 2008 documentation says:

A view is considered an updatable partitioned view when the view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each SELECT statement references one SQL Server base table.

You have two tables in the from clause, so it is not updatable. It is a read-only view. I am not aware that this was changed in 2012.

Answer 2

I faced same issue, to resolve the issue I follow the following steps

1. Create table it does not available

2. Create View against Table

Now If you try to insert or update records via View it will give a error "Update or insert of view or function failed because it contains a derived or constant field"

3. To Resolve this error, need to create Trigger for the above view

4. Try to insert / update records via View

Answer 3

You can work around this by adding an "instead of" trigger to the view and update the underlying tables instead.