Audit Trail

Certified Lead Developer
We have a requirement to create an audit trail in our app that captures every change made on each record at the field level. In addition, we also need to store the original and new value of the field. This is goes beyond the typical audit trail use case that captures the key activities and milestones, not necessarily every data value modified.

We're considering creating DB triggers on the tables storing record data and writing every change to a AUDIT table.

Have others built a similar audit trail? Any recommendations and best practices that can be shared, would be recommended.

OriginalPostID-230702

  Discussion posts and replies are publicly visible

Parents
  • I prefer below 2 solutions each has there own pros and cons

    Implementing Audit Trail Using dedicated Table lets say History and manually making the entry in History table wherever required
    e.g
    Employee
    empId
    name

    EmployeeHistory
    empHistoryId
    empId
    name
    dateAdded

    Pros:
    1. As you are adding the data in EmployeeHistory table you have tight control over what data you need in EmployeeHistory

    Cons
    1. Implementing this for all records and updating all process models may increase development and Testing efforts


    Database Triggers
    1. Implement the database trigger to log the change in history table

    Pros
    1. Easy to implement

    Cons
    1. There might be performance hit if you try to update/insert bulk data. Also if production support team executing any data fix script for large volume of data, you may need to switch off the trigger at that time

    Thanks
    Pratiksha
  • Hi,

    Perhaps a stupid question, but how do you implement the DB triggers and how would you later use that data in your application.

    Let's say, you want to be able to click on a record and see the audit, with this like: when it was modified, what was modified and who modified it.

    Best,
Reply Children
No Data