Auto Auditing on Tables

Question

Auto Auditing on Tables

Christopher Stobbs

SSC-Insane

Points: 21098
More actions
September 23, 2007 at 10:54 am

#177603

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
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life

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

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

Scott Stauffer SSC Rookie Points: 37 More actions · Answer 1

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

Cheers,

Scott

AlanBarber Ten Centuries Points: 1118 More actions · Answer 2

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 INT

to

DECLARE @COLUMNSIZE varchar(100)

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

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

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

WHEN baset.name in (N'decimal')

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

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

END

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') )

BEGIN

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

+') '

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 SSChampion Points: 13729 More actions · Answer 3

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 SSCoach Points: 17998 More actions · Answer 4

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!

Dugi

😉

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

asifejaz SSC Eights! Points: 852 More actions · Answer 5

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.

Thanks

Cheers

billross SSC Eights! Points: 972 More actions · Answer 6

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?

Newbie.

"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com

pramod bura SSC Rookie Points: 39 More actions · Answer 7

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 SSC-Insane Points: 21098 More actions · Answer 8

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

Chris

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

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

SQL-4-Life