locked
Implementing a audit trail for our application RRS feed

  • 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

    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