auditing

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 abou
t
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.
--
Vishal Khajuria
9886170165
IBM Bangalore
"uros" wrote:

> 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 ab
out
> which table and which column is affected.
> So, if i have table employees employeeID,Lastname,Firstname I wolud like t
o
> 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 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
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"uros" wrote:

> 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 ab
out
> which table and which column is affected.
> So, if i have table employees employeeID,Lastname,Firstname I wolud like t
o
> 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?|||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
"uros" <uros@.discussions.microsoft.com> wrote in message
news:0D4071F5-7445-43E1-B904-B4F43DB020FF@.microsoft.com...
> 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?