SQL 2005 DML Triggers Auditing

30 Jun 2006 10:31 AM

Maxus

Hi People,

I was wondering how I could create a generic audit trail trigger for
the tables in my database using SQL DML commands idealy. what I would
like to do is: what changed, when it changed and who changed it. I have
read the following articles:

These two are more for database changes (DDL) than table changes (but
is the kinda thing I would like to do) :
http://techrepublic.com.com/5100-9592_11-6072139.html
http://techrepublic.com.com/5100-9592-6070262-2.html

Exactly the sort of thing I want to do but is it possible to do it
without the CLR (Just worried this is going to be a
perfornace/maintence headache)
http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

This one is really good just wondering if there is a better way in SQL
2005 (this is for 2000):
http://www.nigelrivett.net/AuditTrailTrigger.html (not a big fan of
dynamic Sql)

Any one got a better way of doing it or suggestions on other methods?
Also is it better to put everything in one table or have an audit table
for each table?

Thanks for your help!
-Alex

30 Jun 2006 3:10 PM

Arnie Rowland

The 'best' solution depends upon your needs.

I prefer to have a 'shadow' database for the audit data. Identical table
structure to the tables being audited -with the addition of columns for the
DateOfChange (datetime) DEFAULT getdate(), and WhoChanged varchar(50)
DEFAULT system_user().

Then using FOR INSERT, UPDATE, DELETE Triggers on those tables, move a copy
of the deleted virual table to the appropriate table in the audit db.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Maxus" <rtypestud***@gmail.com> wrote in message
news:1151663475.434945.137100@75g2000cwc.googlegroups.com...
> Hi People,
>
> I was wondering how I could create a generic audit trail trigger for
> the tables in my database using SQL DML commands idealy. what I would
> like to do is: what changed, when it changed and who changed it. I have
> read the following articles:
>
> These two are more for database changes (DDL) than table changes (but
> is the kinda thing I would like to do) :
> http://techrepublic.com.com/5100-9592_11-6072139.html
> http://techrepublic.com.com/5100-9592-6070262-2.html
>
> Exactly the sort of thing I want to do but is it possible to do it
> without the CLR (Just worried this is going to be a
> perfornace/maintence headache)
> http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk
>
> This one is really good just wondering if there is a better way in SQL
> 2005 (this is for 2000):
> http://www.nigelrivett.net/AuditTrailTrigger.html (not a big fan of
> dynamic Sql)
>
> Any one got a better way of doing it or suggestions on other methods?
> Also is it better to put everything in one table or have an audit table
> for each table?
>
> Thanks for your help!
> -Alex
>