I have another question.

I want to create audit table with columns (userID, date, tableAffected,
ColumnAffected).

This table should have data from tables that I want to trace. It easy to
collect data about time and users, but I don't know how to collect data about
which table and which column is affected.

So, if i have table employees employeeID,Lastname,Firstname I wolud like to
have 3 record in my Audit table:

uros; 11/11/2005; employees; EmployeeID
uros; 11/11/2005; employees; Lastname
uros; 11/11/2005; employees; Firstname

Do you have any suggestions?

Hi,
I wat to give my suggestion here , there might be better suggestions pouring
for this prob. so keep checking,

Create trigger for all ur tables and in triggers use Updated(col_name) to
check whether column got updated or not.
Show quoteHide quote

hi Uros,

You can design a SQL server trace using sql profiler
and save the trace to a file.

for optimum performance. you can run a trace
on another machine which is the standard way of profiling sql server.
in this way your auditing doesn't disturb
the production server


Show quoteHide quote

Uros,

I have this design working except I have OldValues and NewValues columns
instead of ColumnAffected. These two columns are for recording affected
columns names and values in an xml style. This allows for having just one
row per action. In addition I have EventType to tell update from insert from
delete and RowID column to link back to the original table record. The work
of writing to the audit table is done in a universal trigger that can be
dropped into a table design with no modifications. In that trigger the
following is user to get the table name:

select  object_name(parent_obj)
from  sysobjects
where  id = @@procid

and columns_updated() function output is parsed to get to the columns
affected.

Ilya

Show quoteHide quote

Cursor working in this stored Procedure??
Best solution, iterate over millions records and call extended sp
convert INT
A difficult Combining Rows problem
very simple query problem