Auto Auditing on Tables

  • Christopher Stobbs


    Points: 21098

    Comments posted to this topic are about the item Auto Auditing on Tables

    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

  • Scott Stauffer

    SSC Rookie

    Points: 37

    Hello Chris,

    Thanks for your script. I had garnished ideas for auditing from SSC a few years back and was quite pleased with my results. I had not used a SProc to create the tables. I did so with system tables and did them in one fowl swoop for the system. incrementally adding new did not warrent the creation of a sproc, but now at a new client site, I want to add the triggers and audit tables, and you have so kindly created a solution.

    One things that I will do for me is add the parameter to allow the user to set the Schema for the Audit Table.

    Another addition that I will make is that I would do the Before and After image on the update. So the Update Trigger will show what the row was before the change, and what it was changed to afterwards. This was the request of my client two years ago and it just gives them a little more confidence and makes it easier to role back a change is they needed to.

    Thanks for sharing your work... much appreciated. Oh. I like the @VbtComplex



  • AlanBarber

    Ten Centuries

    Points: 1118

    Thanks for the cool script Chris,

    I've been looking to build some sort of auditing system for my company for a while now but haven't really thought much about how to easily manage it.

    In playing with the script I found a three small issues I thought I would bring to you.

    First off you might want to declare the @SQLColumn and @SQLColumnSelect variables a bit larger. In testing i found that 1000 characters was not enough for some of the tables in my companies database. create statements were getting cut off.

    We're running SS2k5 Enterprise so i just declared all variables as varchar(max) just so i didn't have to worry. not the best solution but it works ๐Ÿ™‚

    the second issue i found a small bug in that it does not add the column sizes to nchar and nvarchar variables... around line 106 here is the code and what you can add to fix this:

    current code:

    IF ((@COLUMNTYPE='varchar') or (@COLUMNTYPE='char'))

    new code:

    IF ((@COLUMNTYPE='varchar') or (@COLUMNTYPE='char') or (@COLUMNTYPE='nvarchar') or (@COLUMNTYPE='nchar')

    that'll make sure the lengths get attached for nvarchar and nchar.

    finally the create table function is a bit limiting in support for datatypes such as decimals, binary, varbinary so i made some small modifications so it properly creates those variables and adds their limits.

    1) change line 68 from



    DECLARE @COLUMNSIZE varchar(100)

    2) change the @COLUMNSIZE select on line 77 to:

    @COLUMNSIZE=CASE WHEN IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1

    THEN cast((clmns.max_length/2) as varchar(100))

    WHEN in (N'decimal')

    THEN cast(clmns.precision as varchar(4)) + ',' + cast(clmns.scale as varchar(4))

    ELSE cast(clmns.max_length as varchar(100))


    3) change the if statement on line 106 to:

    IF ((@COLUMNTYPE='varchar') or (@COLUMNTYPE='char') or (@COLUMNTYPE='nvarchar') or (@COLUMNTYPE='nchar') or (@COLUMNTYPE='binary') or (@COLUMNTYPE='varbinary') or (@COLUMNTYPE='decimal') )


    SET @SQLColumn=@SQLColumn+'('+CASE WHEN ltrim(@COLUMNSIZE) = '-1' then 'MAX' ELSE ltrim(@COLUMNSIZE) END

    +') '


    making those 3 changes will allow it to properly generate the maxes for binary types as well as do a precision,scale for decimals like "[UserScore] [decimal] (6,2) NULL, "

    Otherwise you have one sweet script and thanks for sharing!

    For my use i'm making a few other changes that will work better for my particual needs. I'm removing the ability to specify the audit database and table names since there is only one for the company and i want to make sure they all go to the right database and follow the correct naming scheme.

    Thanks! ๐Ÿ™‚

  • Christian Buettner-167247


    Points: 13729

    One drawback of these kinds of solutions is their complexity. There is no easy way to customize the triggers for one or more tables.

    I created a similar procedure a few years ago. The bad thing is that I dont completely understand the logic behind this procedure without investing a lot of time into it.

    Nowadays I use the simple approach of having an audit trail table or each single table to be audited. This keeps the process simple - just insert the old record into the audit trail table. And best: Other programmers will understand what is happening in case the need to modify the code in any way.

    Anyways: good work!

    Best Regards,

    Chris Bรผttner

  • Dugi


    Points: 17998

    Hello ...many thanks for this article it is very interesting ...

    but I have one problem with procedure [pAutoAudit] - how do I create it little help couz I'm new here also new SQL user !?

    once again many thanks!




  • asifejaz

    SSC Eights!

    Points: 852

    Hello Chris,

    Its a nice way to audit the db. I am a newbie and also new to SQL Server. please can you explain how to use this script in little bit more detail.



  • billross

    SSC Eights!

    Points: 892

    I'm sorry, where does the database name go in the brackets?

    Is that the only thing one needs to do to this?

    When do i put in the table name?


    "What I lack in youth I make up for in immaturity!"
    Please visit my music site at

  • pramod bura

    SSC Rookie

    Points: 39

    Hi, When I execute the SP with parameters, I am facing the error as

    Msg 536, Level 16, State 4, Procedure pAutoAudit, Line 121

    Invalid length parameter passed to the RIGHT function.

    Msg 536, Level 16, State 5, Procedure pAutoAudit, Line 164

    Invalid length parameter passed to the SUBSTRING function.

    Msg 536, Level 16, State 5, Procedure pAutoAudit, Line 250

    Invalid length parameter passed to the SUBSTRING function.

    Msg 536, Level 16, State 5, Procedure pAutoAudit, Line 338

    Invalid length parameter passed to the SUBSTRING function.

    Pls help me...

  • Christopher Stobbs


    Points: 21098

    Hi all,

    Thanks for the feedback.

    I'll try and answer all the questions here.

    abarber - I have made the changes and bug fixes you suggested, however I'm not sure how to edit the script once I do I'll add the changes

    Dugi - What help would you like?

    billrossbillross- Here is an example of how to use this:

    [font="Courier New"]CREATE TABLE AuditTester


    col1 VARCHAR(10),

    col2 NVARCHAR(10),

    col3 CHAR(10),

    col4 NCHAR(10),

    col5 INT,

    col6 DECIMAL(10,2),

    col7 MONEY,

    col8 FLOAT)

    EXEC [dbo].[pAutoAudit]

    @DATABASE  = 'test'

    , @TABLENAME = 'test'

    , @SCHEMA = 'dbo'

    , @AuditDATABASE  = 'testAudit'

    , @AudiTABLENAME  = 'testAudit'

    , @YourName = 'Christopher Stobbs'

    , @VinJobNo = 1234

    , @VbtComplex = 0[/font]

    asifejaz - What other details would you like to know?

    Have you run the script? If so then seeing the results will give you an understand. If there is more you want to know please let me know.

    pramod bura - Please post the CREAET TABLE structure here so I can see why it isn't working.

    Thanks all


    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]


Viewing 9 posts - 1 through 9 (of 9 total)

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