AutoAudit 3.30a

Rating:        Based on 2 ratings
Reviewed:  1 review
Downloads: 1645
Released: Dec 18, 2013
Updated: Dec 19, 2013 by JohnSigouin
Dev status: Stable Help Icon

Recommended Download

Application AutoAudit 3.30a
application, 323K, uploaded Dec 19, 2013 - 1645 downloads

Release Notes

3.30a Update
  1. Corrected a problem that affected the RowHistory view, the RowHistory UDF and the TableRecovery UDF caused by the existence of Unique constraints or non clustered primary key columns that are not in the first columns of the table or tables with a multiple-column PK that are anywhere or in any column order in the table.

Changes for 3.30
  1. Added the _TableRecovery UDF that returns the data from a table as it existed at a specified point in time
  2. Added User-configurable prefixes and suffixes for AutoAudit views and UDF's
  3. Added a parameter to configure the date style to either 113 or 121 in AutoAudit tables
  4. Added QUOTENAME delimiters around TableName entry in AuditHeader table
  5. Added LoginName to the SchemaAudit table
  6. Changed the default behaviour of the pAutoAuditDropAll stored procedure to 0 (don't drop) instead of 1 and added a safeguard confirmation parameter.
  7. Added the Comment column to the AuditAllExclusions table

Changes for 3.20h
  1. Modified the logic for logging ModifiedBy and ModifiedDate values when @StrictUserContext = 0 during update and delete operations
  2. Added an option in pAutoAuditAll to exclude all tables from specified schemas
  3. Changed the sort order for the _RowHistory UDF from AuditDate,RowVersion to RowVersion,AuditDate

Changes for 3.20g
  1. corrects a minor bug found in 3.20e in the _RowHistory UDF when using column names with spaces and another one related to null values.

Here is a high level list of the things I have changed between AutoAudit 2.00h and 3.20h ...
  1. AutoAudit has been tested on SQL Server 2005, 2008, 2008R2 and 2012.
  2. Added the capability for AutoAudit to handle primary keys with up to 5 columns.
  3. Added the capability for AutoAudit to save changes for a subset of the columns in a table.
  4. Normalized the Audit table and created the AuditHeader and AuditDetail tables. This change reduces AutoAudit storage requirements by over 70% when the SQL statement is not saved and as much as 90% when it is saved.
  5. Added schema support so that the Audit tables and objects can be created in whatever schema the user wants.
  6. Created a _RowHistory view.
  7. Reworked the _RowHistory table-valued functions to reconstruct the full row-by-row details of the audit events when the Logging level is set to 2 (full logging) for Insert, Update and Delete events.
  8. Created a table for AutoAudit system Settings.
  9. Added a table to record the AutoAudit settings for each of the tables that use it. This is used to make it easy to change logging detail levels and rebuild triggers.
  10. Added a flag to the AutoAudit implementation script that determines if the "With Log" function is included in the raiserror statements or not.
  11. Created an upgrade script to migrate existing Audit data to the AuditHeader and AuditDetail tables.
  12. Created a process to move AutoAudit data from the active tables to Archive tables. This process can keep the active tables to a reasonable size and makes it more convenient to partition the archived data as required.
  13. Implemented Quotename() all over to ensure AutoAudit works with any table and column name.
  14. Restructured parts of AutoAudit for performance improvement.
  15. Fixed a few bugs.
  16. Added a table to identify tables to exclude from the pAutoAuditAll SP.
  17. Added switches to enable/disable the DDL trigger and the Audit triggers. Read the warning message in the script file notes before disabling Audit triggers.
  18. The @LogInsert setting (2=details, 1=summary, 0=none) is now available for Update and Delete operations also. Read the warning message in the script file notes before using option 1 or 0 for Update and Delete.
  19. Adjusted AutoAudit to make it work with case-sensitive database collation.
  20. Added the option for users to rename the Created, CreatedBy, Modified, ModifiedBy, RowVersion columns at the beginning of this script to whatever names they want.
  21. Created the pAutoAuditSetTriggerState stored procedure to easily disable and enable AutoAudit triggers for large data imports that do not require auditing to be on.

I have logged over 80 changes. Please refer to the setup script file for all the details. I also added information in the guide section to explain the new features in more details.

Please report any issues to jsigouin.sqldba@gmail.com

Reviews for this release

     
Hello John, you saved me weeks of working. I've fully integrated your solution in my web site for a web deviation management application. Thank you
by accordeon on Feb 1 at 4:37 PM