Suggestions for saving changed data as XML in a Log Table

• samalex

  SSCertifiable

  Points: 5429

  More actions

  September 22, 2010 at 9:43 am

  #238582

  Hi everyone,

  I'm working on an auditing table to save when data is changed, and my thought is to use two triggers - one BEFORE and AFTER - on Insert, Update, and Delete to capture the row info as XML and save it to a Log table with a timestamp and username of who made the change. Here's the code I'm looking at for the XML portion:

  SELECT*

  FROMTableName

  WHERELoanID = 1

  FOR XML AUTO, TYPE, ELEMENTS XSINIL

  Some questions though... the application is web-based so each user is technically logged in using the same Username through IIS, but is there any simple way to pass the user's Application username into the trigger as a parameter or as session ID that could be joined to a Session table to get the User's ID from there?

  Also using this process to save the XML to the table, is there any simple way to parse that data to come-up with a change-log for the table?

  Thanks for any advice... Take care,

  Sam

• subbu_e

  SSCrazy

  Points: 2431

  More actions

  September 23, 2010 at 1:32 am

  #1226122

  you need to pass the user name through your application to your procedure or to your query to capture them

  Regards,

  Subbu

  http://mssqlforum.wordpress.com

  Regards,
  Subbu
  Click here to Get Speedy answer or solution