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:

MyDb.dbo.Customers (
CustName varchar(255) not null,
CustID int not null,
CustAddr varchar(255),
etc.)

Then create:

MyAuditDb.dbo.Customers (
CustName varchar(255),
CustID int,
CustAddr varchar(255),
etc.
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.

Please complete the following information:

REGISTER / LOG-IN TO DISCUSS

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Submit your e-mail address below to continue

E-mail: 

E-mail: 

Handle:   

Password: 

Forgot Password?

Create Handle: 

Your handle identifies you in ITKnowledgeExchange. Max 30 char.

Create Password: 

Confirm Password: 

Yes, I agree to the IT Knowledge Exchange  Terms and Conditions.

hidden modal window