Audit Records

One of the requirements for an application I helpping to develop is
providing audit records of add, updates and deletes including the user who
added, updated and deleted. This application is being sold commerically to
users who have different requirements including not needing audit records.
I was thinking that this could best be done using triggers, having first
requiring the the userid be updated each time. The trigger could simply
copy the row to an audit table for each insert and update. If the user does
not require auditing, don't install the triggers. If the user wishes audit
records only when certain columns have chanaged, then use the if
updated(column_name) logic.
It's the deletes I am having a problem with. I can certainly copy deleted
rows to the audit table, but how would I be able to include the userid of
the user initiating the delete?Inside your delete trigger, you can use USER_NAME() when you go to insert
the deleted row into your audit table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Robert E. Flaherty" <bobflaherty@.charter.net> wrote in message
news:eun5$ZYMFHA.2384@.tk2msftngp13.phx.gbl...
One of the requirements for an application I helpping to develop is
providing audit records of add, updates and deletes including the user who
added, updated and deleted. This application is being sold commerically to
users who have different requirements including not needing audit records.
I was thinking that this could best be done using triggers, having first
requiring the the userid be updated each time. The trigger could simply
copy the row to an audit table for each insert and update. If the user does
not require auditing, don't install the triggers. If the user wishes audit
records only when certain columns have chanaged, then use the if
updated(column_name) logic.
It's the deletes I am having a problem with. I can certainly copy deleted
rows to the audit table, but how would I be able to include the userid of
the user initiating the delete?|||Thanks for the reply. All of the actions are going through an ISAPI DLL
with using standard user id and password. The user's id (which is an
application id and not a MS SQL Server login/userid) is passed on to the
database server as just another data element.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uKURYdYMFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Inside your delete trigger, you can use USER_NAME() when you go to insert
> the deleted row into your audit table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Robert E. Flaherty" <bobflaherty@.charter.net> wrote in message
> news:eun5$ZYMFHA.2384@.tk2msftngp13.phx.gbl...
> One of the requirements for an application I helpping to develop is
> providing audit records of add, updates and deletes including the user who
> added, updated and deleted. This application is being sold commerically
> to
> users who have different requirements including not needing audit records.
> I was thinking that this could best be done using triggers, having first
> requiring the the userid be updated each time. The trigger could simply
> copy the row to an audit table for each insert and update. If the user
> does
> not require auditing, don't install the triggers. If the user wishes
> audit
> records only when certain columns have chanaged, then use the if
> updated(column_name) logic.
> It's the deletes I am having a problem with. I can certainly copy deleted
> rows to the audit table, but how would I be able to include the userid of
> the user initiating the delete?
>|||In that case, you'll have to manage that through a stored proc whereby you
feed the proc the user info.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Robert E. Flaherty" <bobflaherty@.charter.net> wrote in message
news:uuTXcoYMFHA.3336@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. All of the actions are going through an ISAPI DLL
with using standard user id and password. The user's id (which is an
application id and not a MS SQL Server login/userid) is passed on to the
database server as just another data element.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uKURYdYMFHA.2680@.TK2MSFTNGP09.phx.gbl...
> Inside your delete trigger, you can use USER_NAME() when you go to insert
> the deleted row into your audit table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Robert E. Flaherty" <bobflaherty@.charter.net> wrote in message
> news:eun5$ZYMFHA.2384@.tk2msftngp13.phx.gbl...
> One of the requirements for an application I helpping to develop is
> providing audit records of add, updates and deletes including the user who
> added, updated and deleted. This application is being sold commerically
> to
> users who have different requirements including not needing audit records.
> I was thinking that this could best be done using triggers, having first
> requiring the the userid be updated each time. The trigger could simply
> copy the row to an audit table for each insert and update. If the user
> does
> not require auditing, don't install the triggers. If the user wishes
> audit
> records only when certain columns have chanaged, then use the if
> updated(column_name) logic.
> It's the deletes I am having a problem with. I can certainly copy deleted
> rows to the audit table, but how would I be able to include the userid of
> the user initiating the delete?
>|||That is along the lines that I was thinking. The stored procedure designed
to delete a row would first update the UpdatedBy column with the user id of
the user requesting the deletion. The proc would then delete the row. A
trigger could then copy the row to an audit table.
I was wanting to know if there was a better way and/or more standard way of
doing this.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eGGQuuYMFHA.2420@.TK2MSFTNGP10.phx.gbl...
> In that case, you'll have to manage that through a stored proc whereby you
> feed the proc the user info.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Robert E. Flaherty" <bobflaherty@.charter.net> wrote in message
> news:uuTXcoYMFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply. All of the actions are going through an ISAPI DLL
> with using standard user id and password. The user's id (which is an
> application id and not a MS SQL Server login/userid) is passed on to the
> database server as just another data element.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uKURYdYMFHA.2680@.TK2MSFTNGP09.phx.gbl...
>