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?