Skip to Content
author's profile photo Former Member
Former Member

Is it possible to debug the sql server trigger on powerbuilder?

hello sir/madam

I have error of "Row changed between retrieve and update",although I have Google around,but the solution didn't work.(there is a solution in the update properties,in the where clasue for update/delete,choose the Key Columns,I have tried,it doesn't work) after then my workmate just alter the trigger of the table which the datawindow is going to be updated,the error disppeared.this case is weird,because I directly use SQL syntax which is Datawindow generated (I have catched that error in dberror event of datawindow) and the syntax is update mytable set col='some value' where condiction .however the syntax just works fine,the table was been updated.so I wonder if I could debug the trigger of table of SQL Server in powerbuilder?

In addition,I havle written some dlls for powerbuilder to call which is written by the Visual C++,I concern that powerbuilder will execute the function in the DLLs would have issued like the case in above.so I wonder that is it possible that debug the DLL when powerbuider could be trigger the dlls started?

regards

Ken

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Nov 30, 2015 at 02:28 PM

    Hi Ken;

    Most likely, the cause of your troubles started with the way the DataWindow set it's updating criteria. The DW Painter defaults to using the "Key and Updatable Columns" option versus what I always over-ride this to which is "Key and Modified" columns.

    The DW default update will build a DML statement with a Where clause like C1='Value1", C2='Value2", C3='Value3", ... C99='Value99" for example, The problem starts to stem from the fact that the user may have only modified one, two or three columns - but, the where clause checks the database for all possible column's old values. Note: This is of course also a performance handi-cap to check all the previous data values too.

    Now in the case where you have a DB Update Trigger, the Trigger could be modifying for example a "Date_Last_Updated" column. If the DW Object also has this column in its updatable list and the "Key and Updatable Columns" option is in vogue, the Where clause checks the "Date_Last_Updated" column value for 2015-01-01 13:45:10 but the Trigger has already changed the value to 2015-11-30 09:18:46. The WHERE check fails and the DW reports the error "Row changed between retrieve and update". Even though the developer had removed the "Date_Last_Updated" column from the detail band.

    Another scenario might be where you allow the primary key column (or a unique indexed column) value to change. The DBMS can handle this via an Update DML statement or the processing may trigger a DELETE and then an INSERT DML action sequence. The latter scenario inadvertnantly triggering an INSERT TRIGGER even though a DW Update () is in progress. In this case, the previous values to be checked in the WHERE clause are NULL. Again, the DW reports the error "Row changed between retrieve and update".

    The moral of course to your story is that the PB developer and the DBA needs to carefully look at the actions around the Update transaction processing to determine the over-all processing actions being performed at any given time. The DBMS Logs may be the key here in the future. Especially when an SQL trace is being performed from both PB & the DBMS to narrow down the actual cause of the "Row changed between retrieve and update" error.

    HTH

    Regards .. Chris

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 30, 2015 at 02:41 PM

    Eventually you ran into this issue:

    SyBooks Online (Archive) - New RecheckRows database parameter

    hth

    Arnd

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 30, 2015 at 03:01 PM

    You can also try to

    set nocount on

    in your SQL Server trigger code.

    SAP Sybase Forums - PowerBuilder - General Discussion - Row changed between retrieve and update error

    hth

    Arnd

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.