locked
What is the best practice for Audit Trail ? RRS feed

  • Question

  • User-717070177 posted
    Currently I am involving in a project. This project is for my finance division. Because it's recording fund transaction, every record changes must be logged (what're changed? by whom? when?). I have no experience on Best Practice on implementing an audit trail. Currently I am planning to create a copy for each table on database name "audit_<<table_name>>", if the original <<table_name>> is changed then the old record will be copied first to "audit_<<table_name>>" - along with the username who change the record and datetime - before changing the value of original table. But in this way I feel too many resources is spent. Any suggestion? Thanks
    Monday, June 25, 2007 6:24 AM

Answers

  • User1146730029 posted
    Not an easy problem to solve. Have a read of the following link that details two methods of doing this: http://www.4guysfromrolla.com/webtech/041807-1.shtml
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 25, 2007 6:31 AM
  • User1971872635 posted

    I've done this several times in the past and basically done what you're suggesting but with triggers.  IOW, you create an audit table with all the same columns as the original plus a few others (identity column, update date/time filed, operation code - ie, U,I,D for update, insert, delete).  Then, you create 3 triggers on each table, one for inserts, one for updates and one for deletes.  for the inserts & update triggers you insert into the audit table using the "inserted" table.  For the delete table you insert into the audit table from the "deleted" table.

     Performance is excellent, resources are minimal, and you take all the responsibility for mainting the audit record out of the hands of programmers[:)]  For example:

    create trigger dbo.OPER_ID_Insert
            on dbo.OPER_ID after insert
    AS
    SET NOCOUNT ON
    insert into dbo.oper_id_audit (
      timestamp,
      operation,
      .... )
    select
      getdate(),
      'I',
      ...
    from inserted
    go


    create trigger dbo.OPER_ID_Update
            on dbo.OPER_ID after update
    AS
    SET NOCOUNT ON
    insert into dbo.oper_id_audit (
      timestamp,
      operation,
      .... )
    select
      getdate(),
      'U',
      ...
    from inserted
    go

    create trigger dbo.OPER_ID_Delete
            on dbo.OPER_ID after Delete
    AS
    SET NOCOUNT ON
    insert into dbo.oper_id_audit (
      timestamp,
      operation,
      .... )
    select
      getdate(),
      'D',
      ...
    from deleted
    go

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 25, 2007 11:16 AM
  • User1971872635 posted

    In that case you have two create same number of tables with Ins , Del and

    Upd  triggers on each table...??

    That's right!  What else are you going to do?  An audit trail is an audit trail. (Do you really need audit trails on every table or just on the main transaction tables?).  Disks are cheap and if the requirement is to have an audit trail of every update, then you you buy more disks.

    The only other way I know to do this is to track changes field by field (also in triggers) and your audit table just has 5 columns: identity col, date/time, columname, oldvalue, newvalue.  But this is a lot more to code, IMO is error prone, and is very hard to read when you're tracking changes

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 25, 2007 2:29 PM
  • User1974801068 posted

    Read : http://www.tonymarston.net/php-mysql/auditlog.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 13, 2009 7:41 AM

All replies

  • User1146730029 posted
    Not an easy problem to solve. Have a read of the following link that details two methods of doing this: http://www.4guysfromrolla.com/webtech/041807-1.shtml
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 25, 2007 6:31 AM
  • User1971872635 posted

    I've done this several times in the past and basically done what you're suggesting but with triggers.  IOW, you create an audit table with all the same columns as the original plus a few others (identity column, update date/time filed, operation code - ie, U,I,D for update, insert, delete).  Then, you create 3 triggers on each table, one for inserts, one for updates and one for deletes.  for the inserts & update triggers you insert into the audit table using the "inserted" table.  For the delete table you insert into the audit table from the "deleted" table.

     Performance is excellent, resources are minimal, and you take all the responsibility for mainting the audit record out of the hands of programmers[:)]  For example:

    create trigger dbo.OPER_ID_Insert
            on dbo.OPER_ID after insert
    AS
    SET NOCOUNT ON
    insert into dbo.oper_id_audit (
      timestamp,
      operation,
      .... )
    select
      getdate(),
      'I',
      ...
    from inserted
    go


    create trigger dbo.OPER_ID_Update
            on dbo.OPER_ID after update
    AS
    SET NOCOUNT ON
    insert into dbo.oper_id_audit (
      timestamp,
      operation,
      .... )
    select
      getdate(),
      'U',
      ...
    from inserted
    go

    create trigger dbo.OPER_ID_Delete
            on dbo.OPER_ID after Delete
    AS
    SET NOCOUNT ON
    insert into dbo.oper_id_audit (
      timestamp,
      operation,
      .... )
    select
      getdate(),
      'D',
      ...
    from deleted
    go

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 25, 2007 11:16 AM
  • User1875980461 posted

    Hi david ,

    But  is it efficient for large Database with more than say 200 of tables?

    In that case you have two create same number of tables with Ins , Del and

    Upd  triggers on each table...??

    Regards ,

    Rohit

     

    Monday, June 25, 2007 2:19 PM
  • User1971872635 posted

    In that case you have two create same number of tables with Ins , Del and

    Upd  triggers on each table...??

    That's right!  What else are you going to do?  An audit trail is an audit trail. (Do you really need audit trails on every table or just on the main transaction tables?).  Disks are cheap and if the requirement is to have an audit trail of every update, then you you buy more disks.

    The only other way I know to do this is to track changes field by field (also in triggers) and your audit table just has 5 columns: identity col, date/time, columname, oldvalue, newvalue.  But this is a lot more to code, IMO is error prone, and is very hard to read when you're tracking changes

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 25, 2007 2:29 PM
  • User1875980461 posted

    The link sent by Jagdip  have some more gud options to maintain log ..one can read

    the links and according to requirement do logging activity  .

     

    Monday, June 25, 2007 3:00 PM
  • User1146730029 posted
    You should look at your requirements before deciding between the two methods. Both have their advantages and disadvantages (read the link).

    A 'history table for each table' approach would be good if you need the data accessable quickly and easily, or is vital to the application. For example, an accounts package would need this kind of system.

    A 'consolidated history table for all tables' approach would be good if you are only going to access the history once in a blue moon. For example, if you have a package that books badminton courts in a leisure centre (you don't care about who inserted/updated/cancelled and when, only if the court is booked or not).

    Tuesday, June 26, 2007 3:51 AM
  • User2089715135 posted

    If you used database triggers, you will not be able to log some important attributes like username or ip address. In my business class I have a method called archive that gets called everytime an insert, update or delete is performed. It is very simple.

    Monday, July 2, 2007 5:43 PM
  • User1971872635 posted

    you will not be able to log some important attributes like username or ip address

    That's not really true if you make these fields part of what gets updated in the update process.  If you are capturing userid and ip address then obviously it becomes part of the audit trail

    Monday, July 2, 2007 5:48 PM
  • User2089715135 posted

    If I am not mistaken the way the trigger method is described here, the audit functionality resides on the database and is triggered by a database event and is not passed values from the website. How would it get the username and ip of the person who is making the change?

     

    Monday, July 2, 2007 5:56 PM
  • User1971872635 posted

    How would it get the username and ip of the person who is making the change

    Any field that is part of the table will be retained in history.  If userid & IP are fields in the table they are like any other field, there's nothing special about them.  What's confusing you, I think, is that you correctly point out that these data need to be passed in, ie, the trigger has no way to know them by itself

    Monday, July 2, 2007 6:00 PM
  • User2089715135 posted

    I create logs that are summaries of what is done instead so I need to pass data about the current user into logs. That is where my thoughts were differing. You are correct, if you are just transferring the data from one table to another and updating the current record with the user and ip then the triggers will work fine. I was looking at this with a bias toward the way I log transactions.

    Monday, July 2, 2007 10:29 PM
  • User405463548 posted

     Best is use trigger on every table and store information like.

    UserId, IP address, Column Name, Old Value, New Value, ID, DateTime

     

    Maintain a history table for each table.


    Tuesday, July 3, 2007 6:05 AM
  • User-1694337237 posted

     Best is use trigger on every table and store information like.

    UserId, IP address, Column Name, Old Value, New Value, ID, DateTime

     

    Maintain a history table for each table.


     
    I have been looking for this information. Can you provide me with a sample trigger that will pull the information that you list above. I would also like the UserID to be the asp.net user.
     
    Thanks,
    Tuesday, April 28, 2009 10:50 AM
  • User1974801068 posted

    Read : http://www.tonymarston.net/php-mysql/auditlog.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 13, 2009 7:41 AM
  • User652812019 posted

    what if i want to make a database independant audit procedure that is database independant, then i cant use triggers of a particular database format?

    Thursday, May 13, 2010 5:45 PM
  • User-319574463 posted

    what if i want to make a database independant audit procedure that is database independant, then i cant use triggers of a particular database format?

    Auditing needs to be at the database level. Hence if you have a multiplicity of database types, you need to audit in each.


    Saturday, June 12, 2010 9:03 AM