I use a simple approach which works well:
1. Each table being audited should have a PK (or unique constraint). For each table create an audit table of the same schema (columns and data types). Give the audit table a name something like MainTable_A and add 3 extra columns for DateOfChange, Person, Action.
2. Create one trigger for insert, update and delete on the main table. The insert and update actions append the ‘inserted’ rows to the audit table, and the delete action appends the ‘deleted’ rows to the audit table. The extra audit columns can be populated with GETDATE(), SUSER_SNAME() and I, U or D to indicate the action.
The result is the audit table is maintained automatically and there is minimal coding effort. The edit history of each PK can be obtained from the audit table, and the most recent row of a PK value in the audit table will match the current value in the main table. The technique is loosely based on what I’ve seen in commercial apps, also on conversations with colleagues about different ways of implementing auditing,