Here are the options I know of for what you want to do and the SQL Server version(s) it will work on:
1. DDL Triggers - all versions
2. Stored Procedures that do all CRUD operations and do the logging as part of the procedure - all versions
3. Change Data Capture (CDC) - 2008 or later.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question