the PK of datasets is the lsid. that's a combination of id/date and possibly a third key if specified. the audit trail stores this value. the data stored in the audit trail itself is actually pretty nice.

however, if you change the date of a record, or ID for that matter, you LSID changes and I dont see a clean way to find the complete history of this record. is there something i'm overlooking? i'm guessing the audit trail hasnt actually been thoroughly used by a lot of clients, but this seems like a big usability problem. i get that auditing is tough if your PK changes, but lsid is a PK that is inherently prone to change.

in our scenario, we have a second keyfield, a GUID, that's part of lsid. therefore you can search the audit trail using logic like 'find rows where lsid like '%'||objectid||'%', but this query is not very efficient.

is there something better i'm missing?

thanks in advance.