error when inserting into table having instead of trigger from entity data framework

by

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();

More Related Contents:

  1. How to: Create trigger for auto update modified date with SQL Server 2008
  2. Export from SQL Server to Excel
  3. How to get cumulative sum
  4. How to check if a column exists in a SQL Server table
  5. Get first day of week in SQL Server
  6. SQL Server query to find all permissions/access for all users in a database
  7. Infinite loop CTE with OPTION (maxrecursion 0)
  8. How Can I Log and Find the Most Expensive Queries?
  9. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  10. SQL Server add auto increment primary key to existing table
  11. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  12. EF4 – The selected stored procedure returns no columns
  13. How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?
  14. INSERT INTO vs SELECT INTO
  15. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  16. Do while loop in SQL Server 2008
  17. TSQL: How to convert local time to UTC? (SQL Server 2008)
  18. How to update primary key
  19. Case sensitive variables in SQL Server
  20. Transpose rows and columns with no aggregate
  21. SQL Server Configuration Manager cannot be found
  22. SQL Server recursive query
  23. How do I move a table into a schema in T-SQL
  24. nvarchar(max) vs NText
  25. How to find the boundaries of groups of contiguous sequential numbers?
  26. “Prevent saving changes that require the table to be re-created” negative effects
  27. How to change column datatype in SQL Server database without losing data?
  28. Table Valued Function Killing My Query Performance
  29. How to convert text column to datetime in SQL
  30. How to choose the clustered index in SQL Server?

Leave a Comment