Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Auto Auditing on Tables Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 10:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,550, Visits: 2,232
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
Numbers / Tally Tables

SQL-4-Life
Post #401592
Posted Wednesday, October 3, 2007 10:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:58 PM
Points: 10, Visits: 111
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

Post #406317
Posted Wednesday, October 3, 2007 3:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 3, 2011 1:41 PM
Points: 198, 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 :)

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! :)
Post #406475
Posted Monday, November 26, 2007 2:55 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #426053
Posted Monday, December 3, 2007 8:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 3:44 AM
Points: 1,260, Visits: 3,424
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/
Post #428772
Posted Thursday, January 10, 2008 3:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 6:39 PM
Points: 26, Visits: 294
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
Post #441518
Posted Friday, June 20, 2008 5:57 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, March 14, 2010 9:41 PM
Points: 56, 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
Post #521127
Posted Tuesday, October 7, 2008 1:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 3, 2009 9:25 AM
Points: 21, 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...
Post #581549
Posted Tuesday, October 7, 2008 3:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,550, Visits: 2,232
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


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #581625
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse