One very simple approach is to create a parallel set of tables that include some tracking information columns (date/time, action [insert, update, delete], user, etc.). I have done this by creating a separate schema (e.g., “Audit”), and then having triggers that copy the affected data row plus the additional “audit” data into the secondary schema. With this approach, the tables in the second schema can have the same names as in the primary schema.
Note that the secondary schema must NOT have the primary key or other constraints of the primary schema tables, as you will (probably) want to carry multiple copies of the data rows as they change over time.
So, you might have:
CustName varchar(255) not null,
CustID int not null,
AuditDate datetime not null,
AuditAction char(1) not null,
AuditActionBy <userid information> not null);
And the triggers would just copy the data from the primary schema to the secondary schema with the added audit info.
(Tip – use the “after” mode for the triggers – that way the secondary schema tables will contain the history of data including the latest values, so audit queries/analyses won’t need to go against the primary tables at all.)
after insert trigger on MyDb.dbo.Customers
insert into MyAuditDb.dbo (CustName, CustID, CustAddr, etc., AuditDate, AuditAction, AuditActionBy) values (:new.CustName, :new.CustID, :new.CustAddr, …, sysdate(), ‘I’, SYS_CONTEXT(‘USERENV’,’CURRENT_USER’));
You can use the Oracle SYS_CONTEXT function to get various run-time environment settings, like user id or name.