My current shop typically audits primary entity changes with creation and mod dates and users by default. We may apply further auditing but only where there is some particular requirement e.g. price changes. We don't automatically catalogue everything. Our standard reporting solution has record the SQL used for each report which can be helpful.

In general I've found it easiest to stick to using a trigger to record audit details externally to the audited table (obviously this depends, I would not do this for a bulk import table say). I'd certainly consider SQL audit facilities but not quite got there yet.