1. Joe Hess
  2. PowerBuilder
  3. Tuesday, 16 October 2018 18:11 PM UTC

PB2017 R3, SQL Server 2014

 

I'm miigrating an application from PB9 to PB2017 R3. I'm receiving a Row Changed Between Retrieve and Update error when I try to delete a row through a datawindow. I narrowed the problem down to the attached trigger that logs the insert, update, and delete transactions. I believe I was even able to narrow this down to the exact line(s) of code:

select * into #ins from inserted
select * into #del from deleted

When disabled, the delete works properly. If I comment out these SELECT commands and the final EXEC command, I can delete rows. The insert and update statements always work. There is another delete trigger on the table. I tried disabling that trigger while leaving the change tracking trigger enabled, and I still received the error.

Any ideas?

 

Thanks

Joe Hess

 

Attachments (1)
Accepted Answer
Géza Bartha Accepted Answer Pending Moderation
  1. Tuesday, 16 June 2020 09:19 AM UTC
  2. PowerBuilder
  3. # Permalink

Hi!

 

In a trigger shoud use, the

SET NOCOUNT ON option

and row changed between retrieve and update error will be solved.

 

Regards,

Géza.

 

 

 

Comment
There are no comments made yet.
Joe Hess Accepted Answer Pending Moderation
  1. Thursday, 25 October 2018 17:55 PM UTC
  2. PowerBuilder
  3. # 1

Update on my debugging efforts...

 

I figured out if there are no rows written to the temporary file, the delete fails with the ROW CHANGED... error. Here is a simple trigger that causes the delete to fail.

 

CREATE trigger [dbo].[mycolors_ChangeTracking] on [dbo].[mycolors] AFTER insert, update, delete
as

SELECT * INTO #temp FROM mycolors WHERE 1 = 0

 

If I remove the WHERE clause, the delete is successful. So far, this is only through PB2017. PB9 works. Deleting a row through SSMS works. Maybe there is something about SNC that is causing the problem.

 

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 16 October 2018 18:40 PM UTC
  2. PowerBuilder
  3. # 2

Hi Joe;

  Yes, this has normally noting to do with any DB Triggers (although it is possible if the Trigger is a "before Update" one). This is an internal check performed by the DWO's internal code to see if DB column data has changed between when the DWO retrieved the original DB data into its Primary Buffer and how that now compares to the current state of the DB's related potential column updates.

   The DWO's DB updating mentality by default is totally "optimistic" locking approach. That is to say that it tries to select all related data you are requesting "assuming" that nothing else will change the DB's related row data between your changes and the update(s) it needs to process. Any data changes in the DB (or what the DW perceives as a change) is checked. If the original buffer values are not the same as the row being updated - the DWO then errors out on the message that you are getting.

   The default DWO updating scheme that can easily lead to your dilemma, is:  "Key and Updatable Columns". That means that no column in the DWO can have its data changed on the DB before the update will be processed. This is a very risky (optimistic) approach, but can be handled by catching this error in the DC/DS control and then issuing a "ReSelectRow" to refresh the current DWO's Primary/Original buffers to let the App user try the changes again. Normally, most PB developers (hopefully) would choose the update option "Key and Modfied Columns" which can greatly mitigate the DW error message. Especially if any columns are protected from being changed as this limits what the DWO checks during the Update cycle.

   My first suggestion would be to check the Update Properties on your PB 9 based DWO and see if the migrated PB2017 DWO is using the same settings. If not, realign the PB2017 to the PB9's settings & retry. If this is not the case, then try changing the PB2017's DWO "Key and Updatable" update setting to "Key and Modified" and see if that helps. FWIW: I build almost all my App DWO's this way using the "Key and Modified" setting.

   In extreme cases where various Triggers are firing before DB updating or the Apps DWO PowerScript logic is intervening in the UpdateStart/END events, etc - I sometimes have to use a DWO Update setting of "Key Columns" only.

HTH

Regards ... Chris

 

Comment
  1. Joe Hess
  2. Tuesday, 16 October 2018 19:26 PM UTC
Chris,



I had already tried all of what you suggested. I get the same results. I checked the trace to verify nothing was updating the table prior to the final dw.update() command.



As a test, I created a new table called MYCOLORS. It has one column, named color_code. I created the trigger attached to my initial post, minus the final EXEC(SQL) statement. I added three rows to the table. I created a new datawindow object based on the new table. I created a completely new window, not inherited. I added a datawindow control and the script necessary to retrieve the data. I added a command button to delete the currently selected row, and I added another command button to save. I get the same error.



I took this one step further and changed the trigger by removing the delete option. It now is only for insert and update. I can delete a row.



Joe
  1. Helpful
  1. Chris Pollach @Appeon
  2. Tuesday, 16 October 2018 19:42 PM UTC
Hi Joe ... what is the new DWO's "Update Properties" set to?
  1. Helpful
  1. Joe Hess
  2. Tuesday, 16 October 2018 20:21 PM UTC
Allow Updates checked



Key and Updateable Columns selected (I also tried Key Columns, but that really would not matter because my simple table has only one column which is the primary key)



Key Modification set to Use Delete Then Insert
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.