question

savibp avatar image
savibp asked

How to implement audit to store data in Linear data storage approach without using triggers??

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.                    
sql-server-2005audit
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Why do you not want to use Triggers? Triggers would definitely be my preferred method
0 Likes 0 ·
TG avatar image
TG answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AjarnMark avatar image
AjarnMark answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.