Is it possible to Audit a Single table?

• Saravanan_tvr

  SSCarpal Tunnel

  Points: 4558

  More actions

  March 26, 2010 at 6:45 am

  #138030

  Hi,

  Can we record the audit if single table or entire Db updations , Insertions, deletions.. using SQ L audit?

  Actually am everyday updating the table so i need to record some column values

  Example My table is Stock_mst

  code , stock 1,discount , updated_date

  00001 , 12 ,5% , '2010-03-01'

  after am updates

  00001 , 20 ,10% , '2010-03-03'

  after another updates

  00001 , 50 ,20% , '2010-03-12'

  Here I need to record them date wise in to single table is it possible? Please tell me the possible soln.

  Result Am expect

  Recorded_table

  code , stock 1,discount , updated_date

  00001 , 12 ,5% , '2010-03-01'

  00001 , 20 ,10% , '2010-03-03'

  00001 , 50 ,20% , '2010-03-12'

  are any other your Idea share with me.....

• free_mascot

  One Orange Chip

  Points: 27169

  More actions

  March 26, 2010 at 7:18 am

  #1140317

  There are two ways:

  1. Create Trigger on the table

  2. Enable SQL Profiler for only one specific table and desire action.

  HTH

  ---------------------------------------------------
  "Thare are only 10 types of people in the world:
  Those who understand binary, and those who don't."

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 26, 2010 at 7:28 am

  #1140323

  There's a couple levels of auditing to consider.

  using a profiler/server side trace would capture the commands against the table, but not the data itself. That's idea for determining things like who ran what update, performance of those DML changes, etc., but not good for the data changes themselves.

  you'll need to go with free_mascot's trigger suggestion if you need to audit and capture every old value/new value ;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!