Could not refresh after insert when using Instead-of-Insert-Trigger
Posted: Fri 17 Jun 2016 11:21
Hi.
We are migrating from ADO to SDAC. On our DataSets the Refresh.Options is generally set to roAfterInsert, because we have After-Insert-Trigger on some tables which set values in some fields.
Now we have also the case, that on some tables an Instead-Of-Insert-Trigger exists, which inserts the values in the table.
Because of roAfterInsert Options the SDAC DataSet tries to find the inserted value with SCOPE_IDENTITY() but because of the Instead-Of-Insert-Trigger the insert-Statement runs in another scope and so SCOPE_IDENTITY() could not find the row and raised the exception "Refresh failed. Found 0 records".
I found in MSSQLGenerator line 178.
When only the else-statement is executed the refresh succeeds.
Is it possible to solve this issue without modifying the SDAC sources?
We are migrating from ADO to SDAC. On our DataSets the Refresh.Options is generally set to roAfterInsert, because we have After-Insert-Trigger on some tables which set values in some fields.
Now we have also the case, that on some tables an Instead-Of-Insert-Trigger exists, which inserts the values in the table.
Because of roAfterInsert Options the SDAC DataSet tries to find the inserted value with SCOPE_IDENTITY() but because of the Instead-Of-Insert-Trigger the insert-Statement runs in another scope and so SCOPE_IDENTITY() could not find the row and raised the exception "Refresh failed. Found 0 records".
I found in MSSQLGenerator line 178.
Code: Select all
if TMSSQLConnection(GetIConnection).ServerMajorVer > 7 then
FFooterSB.Append(' = SCOPE_IDENTITY()')
else
FFooterSB.Append(' = @@Identity');
Is it possible to solve this issue without modifying the SDAC sources?