How to tell if a table row has been updated

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

     .

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.