Answered by:
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:-
-
the CRUD operation type. is it add, delete or update.
-
the record ID which have been modified.
-
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
Friday, May 31, 2013 9:27 PM -
Answers
-
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
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, June 6, 2013 4:46 AM
All replies
-
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
Tuesday, June 4, 2013 2:42 AM -
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
Tuesday, June 4, 2013 10:21 AM -
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
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, June 6, 2013 4:46 AM