I need to implement audit trail for datbase with following fields.
What data has changed
The date and time it was changed.
The previous value.
The current value.
Who changed the data
The reason for data change.
Contact information of the person who changed the data.
If you don't want to use a trigger to capture that info then I think the only solution (in 2005) is to enforce that all changes occur via stored procedures and put the audit functionality in the SPs. A profiler trace can capture most of it but not the "before" values and you don't want to leave a trace running indefinitely.
If you're willing to spend the money, you could go with a product like Idera's SQL Compliance Manager which does not use triggers, but rather uses a special agent that monitors the trace stream and is able to determine who made what changes when, including old and new values. It also monitors schema changes and has a whole bunch of other whiz-bang features. I'm not a rep for them, I just spent a lot of time looking into auditing products for my company about a year ago and recommended this product.
