Row-level revision control for Oracle?

Question

I'm looking for an oracle package that will capture changes to a table and save them into a log or journal table. For example, executing a sql statements such as

insert into foo(x,y) values (12,34);

would capture these actions into an appropriate table, something like:

timestamp          who     operation   column    value
---------          ---     ---------   ------    -----
12-JAN-2012 13:22  MH      insert      x         12
12-JAN-2012 13:22  MH      insert      y         13

It would be great if there were facilities to generate sql statements based on this information

Answer 1

This can be done using fine grained auditing:

http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm#DBSEG525

As an alternative you can also turn on a flashback archive for the tables in question. Then you can query the content of the table at any point in the past. That won't show you who did the change though (and how it was done).

Answer 2

This can be accomplished by a simple trigger on the target table that fires on insert. Something like this perhaps:

CREATE OR REPLACE TRIGGER foo_audit
AFTER INSERT OR DELETE OR UPDATE ON foo
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        ...
    ELSIF UPDATING THEN
        ...
    ELSIF DELETING THEN
        ...
    END IF;

END;
/

The format of this kind of trigger is sufficiently simple that you could actually programatically generate them.