更新或插入视图或函数失败，因为它包含派生或常量字段 [英] Update or insert of view or function failed because it contains a derived or constant field

问题描述

我一直试图了解以下视图有什么问题，但不幸的是，除了使用我想避免的触发器之外，我无法在任何地方找到我的答案.鉴于以下视图，当我尝试插入它时，我得到了上面的错误，但是如果我删除到 Company 表的内部连接，一切似乎都可以正常工作:

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

这是我的插入，我只插入 [CheckUser] 或 [CheckCompany]:

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 是我的检查约束，有什么办法可以使这个视图与内部联接一起工作?同样，如果我删除内部连接，我可以让它工作，但如果可能的话，我想保留它们.

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.

我使用的是 SQL Server 2012，感谢您的帮助.

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

谢谢，保罗

推荐答案

评论有点长.

我找不到关于这个主题的 2012 年文档，但是 SQL Server 2008 文档 非常清楚:

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

当视图是一个视图时，该视图被认为是可更新的分区视图组合单个结果集的一组 SELECT 语句使用 UNION ALL 语句合二为一.每个 SELECT 语句引用一个 SQL Server 基表.

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.

您在 from 子句中有两个表，因此它不可更新.它是只读视图.我不知道这在 2012 年发生了变化.

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.

这篇关于更新或插入视图或函数失败，因为它包含派生或常量字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！