Tracking database changes.

• lucky-80472

  SSCertifiable

  Points: 7793

  More actions

  March 6, 2004 at 1:18 am

  #161696

  Hello Experts,

  I want some guidance in the area of tracking / auditing changes in the database.

  I am having 9 Master tables, 10 data tables.

  2 audit tables are as follows:

  Audit_Type_Master

  Audit_Type_Id (int), Audit_Type (char(18)) -- Types are inserted, updated and deleted.

  Audit_Master

  Audit_Id (int), Table_Name (varchar(50)), Column_Name (varchar(50)), Table_Row_Id (int), Audit_Type_Id (int), Old_Value (varchar(3000)), New_Value (varchar(3000)), Modified_By (varchar(25)), Modified_Date (datetime)

  The relationship between the two tables is Audit_Type_Id

  The requirement is as follows:

  It is a web-project (ASP, .NET, SQL Server 2000) which will be hosted on a remote server. So i need to track all the changes by way of triggers with respect to each page and also the base table(s) and column(s) with their respective old and new values.

  My questions:

  1. How can i get the session user ie., the person who has logged in?

  2. By way of a trigger on the particular table, only i can hardcode the relevant tablename in the insert statement of Audit_Master. How can i have the other values which are needed?

  3. Does the table design ok or needs a change?

  Thanks for your help.

   

  
  Lucky

• ray_higdon

  SSCarpal Tunnel

  Points: 4589

  More actions

  March 6, 2004 at 6:47 am

  #497704

  1) suser_sname() may help

  See these resources

  http://aspfaq.com/show.asp?id=2496

  http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm

  http://www.nigelrivett.net/AuditTrailTrigger.html

  http://archive.devx.com/dbzone/articles/sh0902/sh0902-3.asp

  http://www.standardreporting.net/survival/view.aspx?_@id=53431

  HTH

  ------------
  
  Ray Higdon MCSE, MCDBA, CCNA