Thanks Tim,

Now that I know this for certain, I've gone off and found/modified some code which creates and updates a log table via a trigger (it also compares old and new values and only records the changes, for UPDATES: INSERTS and DELETES are all recorded). I've written a VFP class that will connect to an ODBC source and automatically create the PL/SQL code for the triggers for each table, so that if I change the tables, I can easily regenerate the code (much as you suggested).

I also read that Oracle 9i has FGA ("fine grained auditing") which I think covers what I am doing now, but my customers only have 8, so I'll be sticking with the triggers.

Many thanks

RJL


>Sounds like you have some trigger that updates a table using the date
>and user all you need to use is the :new.fieldname and :old.fieldname
>values to this
>
>You have a few choices.
>1) Use Oracles internal logging (DBMS_LOGMNR)
>2) Write a trigger to log.
>3) Audit in the application.
>4) Write your own logging procedures (can do complicated things like log attempts to view or drop tables.)
>
>
>I choose to audit in the application but sometimes need to consult the
>databases
>
>A trigger that woul dlog might look something like:
>
>

>create or replace trigger TR_TABLENAME_AUDIT
>AFTER INSERT OR UPDATE ON "TABLENAME" FOR EACH ROW
>BEGIN
>         INSERT INTO AUDIT (LOGDATE,LOGUSER,LOGFIELD,OLDVAL,NEWVAL)
>         VALUES (SYSDATE,USER,'FIELDNAME',:old.fieldname,:new.fieldname);
>
>END;
>/
>SHO ERR
>

>
>I tried once to make triggers that log each field level change
>but found writing one that will work on any table not worth the work so
>when I did this before I wrote code that will generate the triggers based on the column names that can be run when structures change. But all you need
>is the :old and :new values.