How to tell if a table row has been updated - Forum - Community Groups - Progress Community

How to tell if a table row has been updated

 Forum

How to tell if a table row has been updated

  • Is there a way via system tables to tell if a row in a particular table has been update ?

    Any Help Appreciated.

    Jim

  • Updated since when?

  • just the last date and time of the insert or update of a particular row.

    I have a job of keeping two different databases in sync IE: progress ---> postgress

    and I am looking for a way on the progress side since it is the master to determine which rows of

    a particular table to bring over. Rather than just select * from table; And having to spin through

    all the rows. If I can determine the date and time of row updates on the master then I can

    maintain a cache via primary key on the postgress or client db and only pull rows over

    when the update/insert times don't match.

    Jim

  • You'll have to add those timestamps to your schema and maintain them as part of your code. Trigger may be appropriate for that.

  • I never used it before, but is auditing not an option ?

    Gerd

  • If the amount of data that is being updated is small compared with the amount of data on the table, you could use this approach.

    Create an INT64 field on the table.

    Create an index on this field (not unique as there is no real need to pre-populate the field so they'll all be zero or unknown depending on your chosen initial value).

    Create a database sequence, non cycling.

    Create/amend the table''s write trigger to set the INT64 field to NEXT-VALUE().

    The program to select the correct data is then something like:

    FOR EACH

    where GE :

         .

    END.

    This will give you all items updated, but not necessarily in the order that they were done.  Don't be tempted to look for gaps in the sequence as they will occur if multiple updates have happened.

    This is intended to show you that a record has been 'written', not what has been done, when or by whom.

  • Ik ben afwezig. U kunt mij terug contacteren vanaf 05/09/2012 . Voor dringende zaken kan u zich best wenden tot de helpdesk.

    Je ne suis pas présent. Vous pouvez me recontacter à partir de 05/09/2012 . En cas d'urgence vous pouvez vous adresser au 'helpdesk'

    I am absent. You can contact me again from 05/09/2012. In case of urgency you can contact the helpdesk.

    Met vriendelijke groeten / Amicalement / Kind regards

    Gerd

  • Mike,

    Thanks for your help, I like your idea, but I only have read access to the progress db side. Not sure if there

    were any system tables that indicated stuff like a dirty row or other tricks.

    Jim

  • Gerd,

    Thanks for the idea, I don't know enough about the internals of progress

    to know if auditing would work, but I will be looking into that.

    Jim

  • If you can setup a parallel DB, and add some session triggers before the application's main code launches (and the application doesn't use it's own session triggers), you could capture the rows that've been updated that way and save the changes to the parallel DB.

    Failing that, you're stuck with a table scan.