You would write an AFTER INSERT, UPDATE, DELETE trigger. Inside this trigger, you would inspect the :new and :old values of each field to see if they have changed. If they have changed, you would insert the appropriate information into your audit table.
I did something similar to this before. Basically, we had a table TABLE_A and we wanted a history of what changes were made to the data. We created another table AUDIT_TABLE_A that had the same structure as TABLE_A, but contained three additional fields, USERNAME, AUDIT_TIMESTAMP, OPERATION. When an insert happened, we captured the :new values (1 row in AUDIT_TABLE_A). When a delete happened, we captured the :old values (1 row in AUDIT_TABLE_A). When an update happened, we captured both the :new and :old values (2 rows in AUDIT_TABLE_A). AUDIT_TABLE_A was partitioned by day and we kept a rolling window of 30 days in AUDIT_TABLE_A.
Thanks guys - what I had in mind was I'd create a new table, with 2 columns for each column in the audited table, one for the old value, one for the new, rather than 2 rows , one with old & one with new values.
So here is what I came up with:
create or replace trigger tr_aud_table
before update on schema.audit_changes
for each row
insert into schema.audit_changes
NEW_col1 = NEW:col1,
NEW_col2 = NEW:col2,
NEW_col3 = NEW:col3
WHERE ref_number = OLD:ref_number
will this work?
does "USER" pick up the user changing the table?