My company is changing from a distributed Access Database model to using a centralized SQL Database. The datatables were designed such that all of the tables have a modified date. In discussion it was suggested that since we will be creating a trigger on each table to handle the modified date perhaps we should have the trigger also log some information to an audit table.
Is this the best way to setup auditing so that we can track who is changing information or is there a better way? Links to articles on the subject are welcome.
For the audit I'm looking at capturing the table name, column name, date modified, row id and the username of the person making the change. Is there any information I'm not thinking of that I should be capturing that might help me avoid future pit falls?