插入表时出现错误，而不是来自实体数据框架的触发器

2021-12-13 00:00:00 sql-server-2008 sql-server triggers entity-framework-4

我正在使用实体框架 4 ，在使用实体框架在表中插入新记录时，该表具有而不是插入触发器，而表具有标识列，而不是触发器用于根据插入的值修改其中一个值对于某些逻辑，实体框架引发异常存储更新、插入或删除语句影响了意外的行数 (0).自加载实体以来，实体可能已被修改或删除.刷新 ObjectStateManager 条目".

I'm using entity framework 4 , on inserting a new record using entity framework in a table that have instead of insert trigger while the table has an identity column , the instead of trigger is used to modify one of the inserted value according to certain logic ,Entity framework raises exception "Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries".

有人能帮忙解决这个异常吗?

Can any one help how to get around this exception?

推荐答案

使用 Entity Framework 4.1，Ladislav 发布的在触发器主体末尾添加 Select of Scope_Identity() 的解决方案为我解决了问题.为了完整起见，我在这里复制了整个触发器的创建.通过这个触发器定义，我能够使用 context.SaveChanges() 向表中添加行.

Using Entity Framework 4.1, the solution posted by Ladislav to add a Select of Scope_Identity() to the end of the trigger body solved the problem for me. I have copied the entire trigger creation here for completeness. With this trigger defenition I was able to add rows to the table using context.SaveChanges().

ALTER TRIGGER [dbo].[CalcGeoLoc]
   ON  [dbo].[Address]
   INSTEAD OF INSERT
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT OFF;

-- Insert statements for trigger here
INSERT INTO Address (Street, Street2, City, StateProvince, PostalCode, Latitude, Longitude, GeoLoc, Name)
SELECT Street, Street2, City, StateProvince, PostalCode, Latitude, Longitude, geography::Point(Latitude, Longitude, 4326), Name 
FROM Inserted;

select AddressId from [dbo].Address where @@ROWCOUNT > 0 and AddressId = scope_identity();
END

编辑处理计算值(感谢评论中的 Chris Morgan):

Edit for handling computed values (Thanks to Chris Morgan in the comments):

如果表中有任何其他计算值，则也必须将它们包含在 SELECT 中.例如，如果您有一个使用 GETDATE() 的 CreatedDate 列，您可以像这样进行选择:

If you have any other computed values in the table you will have to include them in the SELECT as well. For example if you had a CreatedDate column that uses GETDATE() you would make the select like this: 

SELECT [AddressId], [CreatedDate] from [dbo].Addresses where @@ROWCOUNT > 0 and AddressId = scope_identity();

相关文章