Tracking data changes

18 Aug 2006 8:24 AM

Martin H.

Hello,

I'm trying to understand what is the best way to track data changes in
tables (SQL Server 2005). Meaning i need to store data about who and when
inserted a row, modified a row (including what columns and corresponding
column values) and deleted a row.

And i'd like my application doing these operations, meaning, using some
kind of auditing software is i think not an option.

Now only solution i have found so far, is to make a separate history table
for all tables i need to track data for and use triggers to store needed data.

Is this the only good option or anyone could recommend me some others?

Regards,
Martin.

18 Aug 2006 12:10 PM

Dan Guzman

Triggers/audit tables are commonly used for auditing changes. This is
simple to implement and is the best approach in most situations.

Another method is with third party software (e.g.
http://www.lumigent.com/products/auditdb.html). I have not personally used
any of these so I can't share any experiences.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Martin H." <Mart***@discussions.microsoft.com> wrote in message
news:C059BB2D-412F-4101-92FF-7CD3555AA449@microsoft.com...
> Hello,
>
> I'm trying to understand what is the best way to track data changes in
> tables (SQL Server 2005). Meaning i need to store data about who and when
> inserted a row, modified a row (including what columns and corresponding
> column values) and deleted a row.
>
> And i'd like my application doing these operations, meaning, using some
> kind of auditing software is i think not an option.
>
> Now only solution i have found so far, is to make a separate history table
> for all tables i need to track data for and use triggers to store needed
> data.
>
> Is this the only good option or anyone could recommend me some others?
>
> Regards,
> Martin.