stevegray
4Penny.net
Points: 55409

3/19/2020 8:03:22 AM

sp_CreateAuditScripts2

* This article, and all our great SQL (General) documentation, Is available on the SQL (General) menu

I'm kinda excited about this, but I'm not sure anyone will be but me <laughs>

I help a lot of people with their SQL data. A lot. 

Often, I'll selectively audit some tables so that we can find our where the problems are. 

The technique is to create triggers that run lines from a table into an audit table. Typically if I want to audit SOP10100, I'll create a table called SOP10100AUDIT and save the records there. 

I've done this so many times that I've created a script that does the work of creating the tables and triggers for me, so I can audit a table in seconds. 

Recently I found a customer (unnamed, but Chad knows who he is) that runs a million records a day into my audit table. (sigh)

The problem is that GP updates frequently, and frequently there are no changes to the record, so tons of records exist that need to be sorted through for no reason. Also, at least half the columns in SOP10100 are of no interest, they just take up space in my table and never change. 

The new audit scripts below fix these problems. (There are three scripts, you'll need the bottom function first) The scripts allow for an 'audit database', so that you're not backing up the audit tables if you don't want to. The first script creates a script that creates a 'setup' table. (read that twice).  Run the first script, create the table, then go into it and set the fields that you want to 'true'

Next, run the second script and create the tables and triggers. You're done. Woot!

 

 

4Penny.net
Version: Unknown or N/A
Section: SQL Scripts
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3