We are working on a project at the moment and the client's requirement is that there are multiple audit tables, i.e. an audit log table per source table that "shadows" the source table in terms of fields plus has the additional audit log information such as the UserId for example. They also required that the actual logging happens at a database level using triggers as not all inserts/updates/deletes happen through the business objects. We needed to do this quickly and also have a way of maintaining it when database changes happen. A quick search on the net produced...
Audit Trail Generator for Microsoft SQL
...which was written by Cedric Baelemans. We just needed to modify it to run through a given set of tables, but it works well.
