Tracking Data Changes

From: a (a_at_a.a)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 16:08:30 -0500

I need to log all changes in all tables and in all fields. This includes
inserts, deletes, and updates. Using SQL Profiler doesn't really do what I
need because each time the server is rebooted somebody needs to get Profiler
running again. Also, Profiler seems to stomp on previous log files or
tables which is undesirable. I'm not so sure that Profiler will do a very
good job at logging the changes in such a way as to allow later queries of
the log table to inspect the changes and know exactly what changed, when,
and by who.

I thought about using triggers and the inserted/deleted tables to move the
changed data to a log table, but I have several hundred tables, some tables
with more than 500 fields, so it isn't practical or efficient to write
triggers that have hard-coded field names in them, especially since over
time the tables are likely to change and I don't want anyone to deal with
broken triggers.

The be-all end-all solution I am hoping for is a stored procedure that
doesn't need to know the field names yet gives me a way to associate data
with the field names, is quick and consumes very little processor time, and
logs data to a table that can be queried in such a way that will immediately
show what data changed with each update, what data was inserted, and what
data was deleted, at what time, and by what NT user.

I have looked at bcp and dbcc but neither looks like a good solution for me.
I have seen some third-party software packages out there that cost money,
and read the transaction tables. But the problem is it seems that those are
just showing me the sql statements and not really giving me a way to query
the changes into a report. Also, they cost money and need external support.

Thanks in advance,

z

Relevant Pages

  • Re: Identifying what Apps are using a database
    ... >> capture and activity from Access if they didn't happen ... >> run any Access updates during that time. ... >I would still go with Profiler. ... Then your trace file will not be enormous. ...
    (microsoft.public.sqlserver.server)
  • Re: Identifying what Apps are using a database
    ... > created Access dbs and processes in Access to do updates. ... I would still go with Profiler. ... Then your trace file will not be enormous. ... server with their updates so that you can capture everything in one go. ...
    (microsoft.public.sqlserver.server)
  • Re: Identifying what Apps are using a database
    ... >>I would still go with Profiler. ... Then your trace file will not be enormous. ... >>server with their updates so that you can capture ...
    (microsoft.public.sqlserver.server)
  • Re: Tracking Data Changes
    ... > inserts, deletes, and updates. ... Using SQL Profiler doesn't really do what ... > I thought about using triggers and the inserted/deleted tables to move the ...
    (microsoft.public.sqlserver.programming)
  • Tracking data changes
    ... inserts, deletes, and updates. ... Using SQL Profiler doesn't really do what I ... I thought about using triggers and the inserted/deleted tables to move the ...
    (microsoft.public.sqlserver.datamining)