First, it's not a "n00b" question. There are some complex things that have to be taken into account for this kind of thing.
Now, on to the meat of the question.
There are a number of options.
For "passive logging", you use a log-parsing application to pull audit data out of the log files and backups for the database. This is a very reliable means of getting an audit trail, since the log (so long as you are in Full recovery mode) keeps track of every change made. The reason I call this "passive logging" is that it requires no extra action on your part or on the part of the database. You just buy a program that reads log files and follow the directions and you're good to go. Lumigent, Red Hat and Apex all make very good log parsing applications. The disadvantage to these is that, in my experience, they are slow and somewhat clunky, and you can't use T-SQL to query the log or build reports from it.
For active logging, the usual solutions are either have all of your Update/Delete/Insert procs log the changes, or use triggers to do the same. This adds some processing overhead to the database, and it results in data being stored in standard tables. This has the advantages of being relatively easy to set up (there are products that will set it up for you if you can't do it yourself), easy to query and report against, and looking up row history is as fast as your server can query a table. It has the disadvantages that it does require more CPU and more I/O for the logging, and, if you end up with a dishonest person who has too much access to the log tables, it becomes easy to falsify records.
Personally, I go with active logging for key tables, and passive for the ones that "will never come up". There's a discussion I started a while back on this site (http://www.sqlservercentral.com/Forums/Topic472257-149-1.aspx), on this very subject. In summary, I proposed logging using triggers and XML, and Jeff Moden disagreed with how much I was logging. His solution would take a bit more work to set up and to generate reports from, but would definitely take less disk space, etc.
If you read that and have specific questions about any of it, I'd be glad to help out on them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon