data modifications log

Is there any way to configure SQl in the way
that automatically keep a log of any modification done
into the the data on a specific data table?
fredy,
The way this is usually managed is via triggers. You would create a trigger
on whatever table (or tables) you need audited that will insert the rows
from the 'deleted' table (within the trigger) into a history table (with the
same columns as the base table, usually along with a datetime column for the
date of the modification, etc.) Then you can use the history table to
report on data changes.
"fredy" <anonymous@.discussions.microsoft.com> wrote in message
news:0a8301c4a7d4$c7018f10$a501280a@.phx.gbl...
> Is there any way to configure SQl in the way
> that automatically keep a log of any modification done
> into the the data on a specific data table?
>

Data modification tracking

Hi,

We have a little problem with some data in a particular table. One day the data is ok, and the other day it is not. To be able of trapping the data when it is changing, I was thinking for a trigger.

I could used the profiler, but Profiler tends to show too much data which would be too hard to manage.

With the Trigger, I can go check at the sysprocesses to know which program name has run the problematic query and even capture the computer name. (I haven't construct the trigger yet, but by searching the sysprocesses for runnable process I think I can find the right path to the program running the problematic query.)

The problem is that I will need to know the problematic query. Is there a way to know, within the trigger, which query fired the current trigger ?

Thanks in advance

FrancoisThe trigger can't tell who fired it, but you can easily insert some code in your procedure that will insert an entry into an audit log, or update a LastProcessToMessWithThisData field in your table.

Use the trigger for maintaining to audit columns in your table: Modified (DateTime) and Modifier (nvarchar(50)).

blindman|||The trigger doesn't know what process changed the data, but you can use it to track the system user and the time the change occured.

Try adding some code to your stored procedure so that it logs its execution in an audit table.

blindman|||The trigger doesn't know what process changed the data, but you can use it to track the system user and the time the change occured.

Try adding some code to your stored procedure so that it logs its execution in an audit table.

blindman

data modification logging solution

Anyone has a data modification logging solution such that reverting back is
always an option should a need arises for 'undo-ing' a committed transaction
?
--
bicHi
LogPI http://www.logpi.com
Log Explorer http://www.lumigent.com/products/le_sql.html
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bic" wrote:

> Anyone has a data modification logging solution such that reverting back i
s
> always an option should a need arises for 'undo-ing' a committed transacti
on?
> --
> bic|||Hi,
One solution is to audit the data via triggers . A simple approach (one
that uses lots of space) is to dupicate the existing table and add the
following columns
UserName
ChangedDate
ChangeType
Then create three (or one if you like) triggers which will copy the data
from the event in to the table from the trigger tables(Inseted and Deleted)
This approach does use a lot of space but you can get the rows back very
simply
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"bic" <bic@.discussions.microsoft.com> wrote in message
news:FDB88507-3F35-4F09-9816-58D10E6D9165@.microsoft.com...
> Anyone has a data modification logging solution such that reverting back
> is
> always an option should a need arises for 'undo-ing' a committed
> transaction?
> --
> bic