Devart Forums

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.

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?

Posted: **Fri 17 Jun 2016 12:46**

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.

Posted: **Wed 20 Jul 2016 06:47**

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?

Posted: **Wed 20 Jul 2016 10:08**

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

Devart Forums

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

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

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

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

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