Audit Trail Generator

• Jeff Moden

  SSC Guru

  Points: 1004372

  More actions

  May 20, 2012 at 5:59 pm

  #1490212

  Jeff Moden (5/19/2012)

  ---

  rjs123431 (5/17/2012)

  ---

  Hi All,

  We use sql server 2005 and 2008. I need to trace INSERTED,DELETED and UPDATED records in some tables in this manner.

  - LogID (auto increment)

  - TableName

  - LogType (Insert,Delete,Update)

  - LogDesc

  (Inserted Cust_ID = 001, Cust_Name = John etc....)

  (Deleted Cust_ID = 002)

  (Updated Cust_Name From John to David)

  - LogDateTime

  - LogUser

  I've searched but no luck, samples only shows duplicate table of the audited table. But i only need ONE table for all logs

  Please share your ideas. Thanks.

  Take a look at the example trigger code that G-Squared posted. You'll need something similar to that (without the XML) for every "qualified" column in the table. Of course, you'll need to do some concatenation, as well. With or without XML, that can slow you down a fair bit but not as badly as a supposed "generic" trigger.

  Don't make the mistake of trying to make or use any form of generic audit trigger because that requires making a copy of the INSERTED/DELETED tables because of scope limitations. Combnine that with all the dynamic stuff you need to add in to find out what changed, etc, etc, and you end up with some really slow code which is either memory intensive or Temp DB intensive. I'm replacing some generic SQLCLR audit triggers that some folks put on the main tables at work because they take 4 minutes to process a tiny 10,000 row change of just 4 columns. The new triggers I've written run almost instantaneously. And no, there's no contention on the audit table even though I'm writting hundreds of thousands of rows from each of 8 tables.

  Since I don't want generic triggers, I wrote a stored procedure to create the Audit trigger for a named table.

  Why don't I just post a copy of one of those triggers or the trigger generation proc? Because they don't do what you want them to. I'm pretty sure that you're going to have to write a custom version of all this. I'd do it but I just don't have the time to do it for you. I don't mind helping with any questions you might have along the way, though.

  p.s. I'm actually hedging here a bit. What you want done is going to be pretty awful for inserts and updates performance wise. I'm hoping that you've changed your mind but, if you haven't, post back and we'll see if we can do it and keep the performance problems in check.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)