saving changes made to database

I have an application that connects and edits a database's tables.

My question is, what is the best way to save who did what changes to
this database? I need to be able to display this in the application.
The best way I have thought of so far is, to create a new table with
'user', 'date', 'table_name', 'primary_key', 'old field_value' and
'new_field_value' fields. Then I can save which table row had been
changed, by which user and on which date.

I'm sure that there is a way to do this in SQL Server, but I'm not
sure how.

Any help would be appriciated.

JagdipHi

There is a simple auditing example in Create Trigger topic in Books online,
I would always choose to keep the processing in the trigger to a minimum and
then process the information later as a separate process or when it is
displayed.

For an off the shelf solution you could look at the Lumigent products:
http://www.lumigent.com/Products/

John

"Jagdip Singh Ajimal" <jsa1981@.hotmail.com> wrote in message
news:c84eb1b0.0410010403.a28c3c7@.posting.google.co m...
> I have an application that connects and edits a database's tables.
> My question is, what is the best way to save who did what changes to
> this database? I need to be able to display this in the application.
> The best way I have thought of so far is, to create a new table with
> 'user', 'date', 'table_name', 'primary_key', 'old field_value' and
> 'new_field_value' fields. Then I can save which table row had been
> changed, by which user and on which date.
> I'm sure that there is a way to do this in SQL Server, but I'm not
> sure how.
> Any help would be appriciated.
> Jagdip