Could not refresh after insert when using Instead-of-Insert-Trigger

[dupe]

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.

Code: Select all

if TMSSQLConnection(GetIConnection).ServerMajorVer > 7 then
  FFooterSB.Append(' = SCOPE_IDENTITY()')
else
  FFooterSB.Append(' = @@Identity');

When only the else-statement is executed the refresh succeeds.

Is it possible to solve this issue without modifying the SDAC sources?

[ViktorV]

We will consider the possibility to change this behavior of SDAC.
For the time being, you can change the SDAC source code only by yourself to get the needed functionality.

[dupe]

I'm glad to see you offer the possibility to use the option UseSessionIdentity to satisfy this request.
Unfortunately in the meantime we find out that @@Identity also do not fulfil our requirements.

In some cases their are AFTER-INSERT-Trigger in our database which inserts data in other tables. In these case @@Identity determine the primary key of the other table, but not the originaly inserted data.

To solve this we use IDENT_CURRENT('tablename').

Code: Select all

FFooterSB.Append(' = IDENT_CURRENT(''');
FFooterSB.Append(SQLInfo.NormalizeName(tableName, QuoteNames));
FFooterSB.Append(''') ');

Would you also consider to implement this functionality?

[ViktorV]

We will consider the possibility to implement this functionality in one of the next SDAC versions.