Implementing a audit trail for our application

Question

User-540818677 posted

I want to add an audit trail for our system so when any Add/Delete/Update operation happen i will log it, with the following info:-

1. the CRUD operation type. is it add, delete or update.

2. the record ID which have been modified.

3. Date and time.

Now i found two approaches to follow; either to have a single audit trail table with the following fields:-

• ID .such as 123445.
• CRUD_description. Such as Delete
• Record_ID. Such as Qaeop12771
• Date. Such as 1june2O13

Or to have two tables one for a lookup table for the CRUD operation such as

• CRUD_ID. such as 3.
• CRUD_Description.such as Delete.

And then the Audit trial will reference the above table:-

• ID. such as 123445.
• CRUD_ID (this will be a foreign key to the CRUD table) such as 3.
• Record_ID. Such as Qaeop12771
• Date. Such as 1june2O13

So which approach is better ??

Second question If i will follow the second approach . Then is it preferred to use the CRUD_ID inside my code for example if the oprration is delete i might have my code look like:-

Inset into audit_trail (ID, CRUD_ID, Record_ID, Date) values ( 123445, 3,12771,1june2O13)

// CRUD_ID 3 represents delete opration

Best Regards

Answer 1

User-646145796 posted

hi,

if we want to add an audit trail for our system

we only need one table to record it

the table need

1.the key of this table

2.the foreign key that is the key of the table we oprate

3.the type we oprate

4.time and date

I think this is enough.if you have some different opinion,please tell me.

Hope it can help you

Answer 2

User-540818677 posted

thanks for thr reply,,,, but can you provide a sample of the data for a record in the table ? as i can not understand the differences between the first & the second columns:-

1.the key of this table

2.the foreign key that is the key of the table we oprate

Answer 3

User-646145796 posted

hi,

create table AuditTrail
(
Audit_id int identity(1,1)  primary key , --!the first one
Record_id int,  --! the second one
OprateType nvarchar(50),
OprateTime datetime
)

Best Regards