Capturing User Info on updates to an audit table

smjg · December 13th, 2012, 01:36 AM

I've used your book as the basis of my website, and the book was very helpful in all aspects of my development, but I'm stuck trying to add some auditing features to my site. I've created a simple table that contains a character field and an integer field, and on that table I've created a simple trigger that fires on any updates to an audit table that i want to hold both the old and new values of the integer, along with the system time and the user that did the actual update. While I'm capturing both the old and the new values, along with the system time, I'm lost as to how I would capture the user that did the update. My site uses the standard asp.net login controls, so I can capture the user in the code behind, and the update is being done via a standard gridview. My trigger can use SUSER_SNAME(), but that is capturing the actual pc name and not the user name. I'm unsure if i can capture this info in the sql trigger. I was thinking maybe i somehow try to join with the asp.net security db or try using the profile.user.name or something along that line. Any thoughts on how i would go about it?

Thx Again

Imar · December 14th, 2012, 03:57 AM

Hi there,

For questions not directly related to the book, you're better off posting in a more general ASP.NET forum from this list http://p2p.wrox.com/asp-net-4-539/ as you'll attract more viewers.

That said, you can pass the user name along with your insert and update statements. You find an example here: http://imar.spaanjaars.com/571/letti...t-45-web-forms Although the article discusses using the user name as a filter, you can also use it in INSERT and UPDATE statements.

Cheers,

Imar

smjg · December 14th, 2012, 05:29 PM

Thx, will post any further questions there