SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Auto Auditing on Tables


Auto Auditing on Tables

Author
Message
Christopher Stobbs
Christopher Stobbs
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5656 Visits: 2233
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
Numbers / Tally Tables

SQL-4-Life
Scott Stauffer
Scott Stauffer
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 116
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
AlanBarber
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
Points: 350 Visits: 199
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 Smile



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! Smile
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5357 Visits: 3889
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
Dugi
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4838 Visits: 3511
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
Wink

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
asifejaz
asifejaz
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 356
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
billross
SSC Veteran
SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

Group: General Forum Members
Points: 236 Visits: 119
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
pramod bura
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 32
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
Christopher Stobbs
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5656 Visits: 2233
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:

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






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
Numbers / Tally Tables

SQL-4-Life
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search