Symptom

Getting "Row changed between retrieve and update" error message when updating a table that has triggers.

Environment

PowerBuilder

Microsoft SQL Server

Reproducing the Issue

To reproduce this issue:

1) Perform an INSERT, UPDATE or DELETE on a Microsoft SQL Server table with a trigger (see Cause)

2) Connect with either of the following database interfaces:

·         ADO.NET

·         OLE DB

·         SNC SQL Native Client for Microsoft SQL Server

Cause

The following is from the PowerBuilder Help for the RecheckRows database parameter which is set to zero (0) or disabled by default.

In Microsoft SQL server, if a table has an insert, update, or delete trigger, the number of affected rows returned to the SQLNRows property of the Transaction object after an INSERT, UPDATE, or DELETE command depends on the driver. With an ADO.NET driver, the value returned is the sum of the rows affected by the command itself and the trigger.

When you are connected to Microsoft SQL Server using ADO.NET or OLE DB, you can set the RecheckRows runtime database parameter to 1 to recheck how many rows of data were affected by the INSERT, UPDATE, or DELETE command itself and return that value in the SQLNRows property.

This also applies to the SNC SQL Native Client for Microsoft SQL Server.

Resolution

Setting the database parameter RecheckRows=1 solves the problem.