What is the best practice for Audit Trail ?

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

Answer 1

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

Answer 2

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

 

Answer 3

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

 

Answer 4

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

Answer 5

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  .

 

Answer 6

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).

Answer 7

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.

Answer 8

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

Answer 9

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?

 

Answer 10

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

Answer 11

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.

Answer 12

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.

Answer 13

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,

Answer 14

User1974801068 posted

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

Answer 15

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?

Answer 16

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.