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

score 20 · Accepted Answer · edited Jul 12 '22 at 13:20

20

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.

edited Jul 12 '22 at 13:20

Prisoner ZERO

13,848
21
92
137

answered Aug 24 '14 at 14:02

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon, got any suggestions on how to go about it? The reason I need a view is because I need a unique key between a field in CheckUser/ CheckCompany and another field in one of the tables I will be joining with. Or perhaps I am going about it all wrong and should not be using views at all? The other option would be to combine these two tables into a Check table, however I would still need to somehow ensure uniqueness between a field in Check and a field in a second table. – user3800174 Aug 24 '14 at 14:31
@user3800174 . . . Can you create the updatable view without the join and then use another join to bring in that information? Another option is to use an `instead of update` trigger on the view. – Gordon Linoff Aug 24 '14 at 16:10

score 8 · Answer 2 · answered Feb 02 '16 at 11:53

8

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

answered Feb 02 '16 at 11:53

Erik Grahn

81
1
1

score 0 · Answer 3 · answered Dec 07 '21 at 12:41

0

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

Create table it does not available

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"

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

Try to insert / update records via View

answered Dec 07 '21 at 12:41

Gehan Fernando

1,221
13
24

1

Please replace your images with text. It is not searchable, copyable, or screen-readable. – Andrew Aug 10 '22 at 17:28

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

3 Answers3

Linked