Humprey Evangelista Cogay

My Programming Adventures

SQL Audit Trail

I was asked by a friend of mine on how to create a trigger that would trace the changes that a user did to a table. So i checked my bookmarks and give him this link

http://www.nigelrivett.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html

Hope to Get some of your opinions or maybe improvements on the topic inside the link above.

 

God Bless...

Posted: 09-13-2008 12:04 PM by Comgen | with 6 comment(s)
Filed under: , , , , , ,

Comments

cruizer said:

my comment is that it does not record the user who performed the action, unless you connect to the db using the account of the user executing the app itself. for a desktop app, that's fine although you cannot pool database connections if that approach is used. for web apps or WCF app services, you cannot use it unless you employ delegation on the app server and the database server.

# September 14, 2008 3:13 AM

Comgen said:

Thanks sir Cruizer.... for web apps? what would be the common/ussual solution for  logging audit trail? is it creating a log class instead of using Triggers?

# September 15, 2008 6:00 AM

keithrull said:

If you are using SQL Server 2008 you can use the new feature called Change Data Capture.

msdn.microsoft.com/.../bb522489.aspx

Basically, the idea behind this feature is that the server should be able to watch changes to the data. This feature is highly configurable and would fit from the basic logging to advance logging mechanics.

HTH

# September 15, 2008 8:39 AM

cruizer said:

personally i still prefer doing audit logging within a stored procedure itself, e.g. a stored proc for inserting a record into the Users table would be responsible for executing a stored proc for inserting into an audit table within the same database transaction.

it's quite manual (and repetitive) but it gives better control over the audit logging process.

# September 17, 2008 12:37 AM

Comgen said:

Great point Sir Cruizer... will take note of that....

# September 29, 2008 7:43 PM

Comgen said:

Thanks sir Keith.. Will check that out...

# September 29, 2008 7:44 PM