Audit Trails And SQL

I am developing an ASP application which will interact with a SQL database. A requirement of the application is that there is a full audit trail of any modifications to data. I am struggling a bit to get my head round how to do this - just the concept.

Should I create dummy tables, and when a users asks to modify something, make a copy to the dummy table with date/time/user etc., or is there another recommended way? I have never built in an audit trail before, and have specifically been asked on this occasion, that every single field which is able to be modified by a user can be tracked.