1

One of my tables in SQL Server, has an InsteadOfInsert trigger defined, which I cannot modify. EF 6, will generate a correct 

INSERT [SomeTable] ([SomeColumns]) VALUES (SomeValues)
SELECT [SomeIdenityColumn], [ROWID]
FROM [SomeTable]
WHERE @@ROWCOUNT > 0 AND [SomeIdenityColumn] = scope_identity()

but this call fails miserably, due to scope_identity() being null when coming out of the InsteadOfInsert trigger, which I cannot modify. Is there any way in EF 6 to modify the generated SQL to utilize the OUTPUT Inserted approach instead?

UPDATE

Found these two on SO:

OptimisticConcurrencyException -- SQL 2008 R2 Instead of Insert Trigger with Entity Framework

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

UPDATE 2 - AKA REALLY BAD NEWS

So I have modified the generated SQL code using SSMS in an attempt to utilize INSERT INTO OUTPUT.

INSERT INTO [SomeTable] ([SomeColumns]) 
OUTPUT Inserted.[SomeIdenityColumn], Inserted.[ROWID]
VALUES (SomeValues)

When executing from SSMS I get this error:

Msg 334, Level 16, State 1, Line 41 The target table 'AuMine.QUOTE_STAFF_ACTIVITY' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Classic catch 22 ..

Community
  • 1
  • 1
Darek
  • 4,687
  • 31
  • 47
  • Seems like I am not the only one: http://bobondevelopment.com/2011/05/26/sql-server-instead-of-trigger-gotchas-revealed/ – Darek Aug 13 '14 at 14:09
  • The problem description doesn't make sense. [`SCOPE_IDENTITY`](http://msdn.microsoft.com/en-GB/library/ms190315.aspx) is used, specifically, because a trigger has a *different* scope and so no action taken within the trigger should affect its value. Are you sure you've correctly diagnosed the actual issue here? – Damien_The_Unbeliever Aug 13 '14 at 14:20
  • Yup, executed the EF generated SQL from SSMS and saw it with my own eyes ... Other posts confirm that using InsteadOfInsert trigger, pretty much kills EF. Apparently it is a known "feature" – Darek Aug 13 '14 at 14:23
  • Add just to clarify, I have no control of the trigger, and the trigger does an update on another table, than insert on the original one. SCOPE_IDENTITY() is available withing the trigger, but not outside. It is simply NULL – Darek Aug 13 '14 at 14:28

0 Answers0