Post reply

Question on implementing audit trial using triggers

  • May 16, 2006 at 5:51 pm

    #93983

    I'm investigating options to implement audit trial using triggers and hoping for some advice. The requirement is that I store old values and new values, along with the originating query in a table.

    Question 1.

    I'm trying to capture the complete query from EventInfo from INPUTBUFFER. But it seems to give me only the first 255 characters if not less. Is there a setting that I can tweak to get the full query that very often goes beyond 255 characters?

    Question 2.

    For update queries, is there a way to find out the exact column being updated from the 'inserted' table? The target table has close to 100 columns. I can test using a long list of 'IF UDPATE() to test which columns are being updated but since this is done in a trigger, it would be too costly to have 100 IF's. I'm aware that I can be store the before and after images of the entire row being updated but that's coastly too. I'm think of storing the column name, old value and new values in the audit table only.

    Would someone please give me some suggestions? I'm sure there is better way of meeting these requirements.

    Thanking in advance.

  • May 19, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • May 22, 2006 at 7:48 am

    #639229

    Hi,

    This can be as complicated as you want it to be.

    One idea is to have INSERT, UPDATE, DELETE triggers on your tables that capture Tablename, Column, PK Value, Old VAlue, New Value.

    You could capture the entire row if you wish.

    Depends on what you want to do with the Audit Log.

    HTH

    Graeme

  • May 22, 2006 at 7:58 am

    #639232

    Sorry...missed a bit.

    You can create a script that generates the triggers automatically and writes the entire row before change into a column and the entire row after change into another column. You are , of course, limited by the amount of data you can get into one column.

    Regards...Graeme

  • May 22, 2006 at 9:27 am

    #639302

    http://mindsdoor.net/SQLTriggers/AuditTrailTrigger.html

    http://mindsdoor.net/SQLTriggers/Triggers_2_Creating_Audit_Trails.html

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • May 22, 2006 at 10:54 am

    #639328

    Thanks to everyone, especially to Ryan. The solution in the first article works very well for our needs.

  • May 22, 2006 at 11:10 am

    #639332

    I guess it's Nigel you should be thanking!

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply