Auditing data on SQL 2000

28/09/2007 - 08:51 por Erick | Informe spam
Hi,
i am trying to create a generic trigger which will audit
changes to data in a table.
Now it is possible to know which column has been changed by using
Update() or Column_update()

But even with dynamic SQL i can't figure out how to insert the values
i want into an audit table.

Inserting DateTime of Change and the user name is easy. But only
during run time after I have checked will i know which column has had
it's valued changed. And there could be multiple columns whcih have
changed.

I want to insert information about each changed column into an audit
table (TableName, FieldName, Oldvalue, Newvalue,Timeofchange,
Changedby).

All tables would have a copy of this trigger but all would write to a
single Audit table in the database.

How do you write the insert statement for the audit table so it picks
up those columns in the inserted and deleted tables which have
changed. e.g. 3 columns updated in one row should create 3 records in
the audit table

regards

Erick
 

Leer las respuestas

#1 Gustavo Larriera (MVP)
28/09/2007 - 16:29 | Informe spam
Por favor escriba en español en este foro.

Mi sugerencia es que centralice su proceso de auditoria en un procedimiento
almacenado. Este procedimiento recibe en sus parametros los datos que hay que
auditar. Luego ejecute el procedimiento desde cada trigger de auditoria.

Gustavo Larriera, Microsoft MVP
https://mvp.support.microsoft.com/p...o.Larriera
Este mensaje se proporciona tal como es, sin garantías de ninguna clase.



"Erick" wrote:

Hi,
i am trying to create a generic trigger which will audit
changes to data in a table.
Now it is possible to know which column has been changed by using
Update() or Column_update()

But even with dynamic SQL i can't figure out how to insert the values
i want into an audit table.

Inserting DateTime of Change and the user name is easy. But only
during run time after I have checked will i know which column has had
it's valued changed. And there could be multiple columns whcih have
changed.

I want to insert information about each changed column into an audit
table (TableName, FieldName, Oldvalue, Newvalue,Timeofchange,
Changedby).

All tables would have a copy of this trigger but all would write to a
single Audit table in the database.

How do you write the insert statement for the audit table so it picks
up those columns in the inserted and deleted tables which have
changed. e.g. 3 columns updated in one row should create 3 records in
the audit table

regards

Erick


Preguntas similares