Re: Audit Trail -> Historical table view

"Peter Scott" <peter_at_PSDT.com> wrote in message news:Bap6c.102859$Up2.49189_at_pd7tw1no...
| I can't believe I've been using Oracle this long without needing to
| do this. The database I am using defines audit trails on most tables;
| original table plus audit id, timestamp, and operation. Great for
| finding out who did what when.
|
| But right now what I want to do is a query on what a particular
| table would have contained on a given date. The information is
| in the audit table, but it looks like a nightmare to try and do
| this given the form of that data.
|
| I thought this would have been a FAQ but I cannot find it.
| Any ideas?
|
| --
| Peter Scott
| http://www.perldebugged.com/
| *** NEW *** http://www.perlmedic.com/

shouldn't be too bad, should probably look something like this, although the operation and whether you're storing before or after values needs to be considered

select a1.*
from atable a, (

    select id, max(audit_timestamp)
    from atable
    where audit_timestamp <= some_point_in_time ) a2
where a1.id = a2.id and a1.audit_timestamp = a2.audit_timestamp

(sample code not testing, supplied to illustrate a possible approach)

;-{ mcs Received on Fri Mar 19 2004 - 19:42:07 CST