none
How to implement audit/history feature on every insert update delete operation? RSS RRS feed

  • Question

  • I want to implement some kind of a historification/auditing feature. I am using Repository Factory for data access. And for every CUD operation I want to store information in one (only one table). Do you know some practical implementations of this.
    thank you.
    Friday, June 20, 2008 8:02 AM

All replies

  • What you could do, is whenever you do a CUD operation, serialize your object to XML, and store this in your audit table.

    So you would have a table like;
    CREATE TABLE tblAudit
    ID INT IDENTITY
    AuditData NVARCHAR (Or could use XML if you do not need to support pre-SQL 2005
    User NVARCHAR (Or INT if you are storing user id etc)
    AuditDate DATETIME



    Then you just need to serialize your object to XML (see this KB article):

    I would suggest you perform these actions in your DL implementation, i.e.

    BLL
    MethodA(object)
    {
    DL.MethodA(object)
    }

    DL
    MethodA(object)
    {
    // your code for MethodA
    InsertAuditRecord(Serialize(Object))
    }

    or, expose an audit method in your DL, like so

    BL
    MethodA(object)
    {
    DL.MethodA(object)
    DL.Audit(object)
    }

    Good luck!
    Friday, June 20, 2008 10:04 AM
  • That is what I am preparing for. Just wanted to know if there are some best practices like History table fields and field types.
    My plan it to call these methods from the Business Logic Layer where I am managing transaction.
    If you have other suggestions I will be glad to read them

    Friday, June 20, 2008 10:13 AM
  • Can you use triggers to catch changes against database? It would be preferable way to implement auditing, since it would be cenralized place and you do not need to execute anything specific from your application code.

     

    Friday, June 20, 2008 10:22 AM
    Moderator
  • I know the possible solution with triggers but I am against this. I do not want to load the database more. And it is more scalable within the business logic layer. Thanks anyway
    Friday, June 20, 2008 10:25 AM