SQL 更新或插入视图或函数失败，因为它包含派生或常量字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25472378/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update or insert of view or function failed because it contains a derived or constant field
提问by user3800174
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:
我一直试图了解以下视图有什么问题，但不幸的是，除了使用我想避免的触发器之外，我无法在任何地方找到我的答案。鉴于以下视图，当我尝试插入它时，我得到了上面的错误，但是如果我删除了 Company 表的内部连接，一切似乎都可以正常工作：
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]:
这是我的插入，我只插入 [CheckUser] 或 [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.
CheckToTypeID 是我的检查约束，有什么方法可以使此视图与内部联接一起使用？同样，如果我删除内部连接，我可以让它工作，但如果可能的话，我想保留它们。
I am using SQL Server 2012, any help is appreciated.
我正在使用 SQL Server 2012，任何帮助表示赞赏。
Thanks, Paul
谢谢，保罗
采纳答案by Gordon Linoff
This is a bit long for a comment.
评论有点长。
I cannot readily find the 2012 documentation on this subject, but the SQL Server 2008 documentationis quite clear:
我无法轻易找到关于这个主题的 2012 文档，但SQL Server 2008 文档非常清楚：
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.
当视图是一组 SELECT 语句，其各个结果集使用 UNION ALL 语句组合成一个时，该视图被视为可更新的分区视图。每个 SELECT 语句引用一个 SQL Server 基表。
You have two tables in the fromclause, so it is not updatable. It is a read-only view. I am not aware that this was changed in 2012.
from子句中有两个表，因此它不可更新。它是只读视图。我不知道这在 2012 年发生了变化。
回答by Erik Grahn
You can work around this by adding an "instead of" triggerto the view and update the underlying tables instead.
您可以通过向视图添加“代替”触发器并改为更新基础表来解决此问题。
