Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Inserts and triggers



Permlink Replies: 2 - Last Post: May 20, 2016 2:08 AM Last Post By: Anders Gustavsson
Anders Gustavsson

Posts: 26
Registered: 9/26/99
Inserts and triggers
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 19, 2016 2:38 AM
Yesterday i was struggling with a problem I didn't understand. As I found out this morning, I would like to write about it. Maybe the problem is hard to resolve in a safe way, but if someone else runs into this, here are some clues:

Scenario: TFDQuery connected to MS SQL connection. In database I have a table with After Insert Trigger that changes column value depending on some condition on in-data.

Doing: Inserting a record and posting in Query object.
Result: Error 400: updated [0] when [1] expected - but record is actually posted and all seems just fine in database.

After experimenting with the insert-query in SQL Management Studio I see every insert is followed by the message "Updated 1 row" twice! And here it is: the insert itself is reported once - and the trigger update after that once.

Now, what happened in my strange scenario, was that the trigger condition didn't cause the trigger to update anything, and thus the second message is that zero rows were updated. And to my understanding, this last 0 is what the TFDQuery component gets in return and hence the error message.

Changing the trigger to update in all cases - even if no update was necessary - solves the problem.

Maybe it would have been a good thing if Firdac had differentiated between the 2 return values, but it is beyond my knowledge whether this is possible or not. If this little story can give anyone else a hint, it is fine with me...

/Anders
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Inserts and triggers
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 19, 2016 11:51 PM   in response to: Anders Gustavsson in response to: Anders Gustavsson
FireDAC cannot differentiate these two updates. More precisely,
the MSSQL ODBC driver cannot. The only solution will be to set
UpdateOptions.CountUpdatedRecords to False:
http://docwiki.embarcadero.com/Libraries/Berlin/en/FireDAC.Stan.Option.TFDUpdateOptions.CountUpdatedRecords

--
With best regards,
Dmitry
Anders Gustavsson

Posts: 26
Registered: 9/26/99
Re: Inserts and triggers
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 20, 2016 2:08 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
FireDAC cannot differentiate these two updates. More precisely,
the MSSQL ODBC driver cannot. The only solution will be to set
UpdateOptions.CountUpdatedRecords to False:
http://docwiki.embarcadero.com/Libraries/Berlin/en/FireDAC.Stan.Option.TFDUpdateOptions.CountUpdatedRecords

--
With best regards,
Dmitry

Thought so :-) Thanks for your quick answer!

/Anders
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02