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开发者_JS百科 loaded. Refresh ObjectStateManager entries".
Can any one help how to get around this exception?
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
Edit for handling computed values (Thanks to Chris Morgan in the comments):
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();
Instead of trigger is executed instead of Insert operation created by Entity framework. This can be potential problem because once you are using identity column each insert is followed by:
select [Id]
from [dbo].[TableXXX]
where @@ROWCOUNT > 0 and [Id] = scope_identity()
So the question is what happens with this query once insert is replaced. If it is executed and return null you get and exception. You can add it after you insert record in your trigger but it will not help if the original query is executed as well.
You can change your trigger to be either before or after insert and modify data.
You also need to return any properties marked as Computed
select [Id], [YourComputedColumn]
from [dbo].[TableXXX]
where @@ROWCOUNT > 0 and [Id] = scope_identity()
I also found that you need to have the StoreGeneratedPattern set to Identity to get it to work on a nvarchar column that I was using as a primary key but which was not generating an identity value. This was for situations of an after insert trigger which computed a unique value to store in the key column. In other situations (add and update), it may require being set to Computed.
精彩评论