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

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
dupe
Posts: 20
Joined: Wed 15 Jun 2016 13:37

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

Post by dupe » 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?

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

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

Post by ViktorV » 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.

dupe
Posts: 20
Joined: Wed 15 Jun 2016 13:37

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

Post by dupe » 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?

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

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

Post by ViktorV » Wed 20 Jul 2016 10:08

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

Post Reply