AutoAudit 3.20h

Rating:        Based on 1 rating
Reviewed:  1 review
Downloads: 280
Released: Nov 19, 2013
Updated: Nov 19, 2013 by JohnSigouin
Dev status: Stable Help Icon

Downloads

Source Code AutoAudit 3.20h
source code, 276K, uploaded Nov 19, 2013 - 280 downloads

Release Notes

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 a total of 71 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.

Notes:
3.20g
- 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.
3.20h
- Modified the logic for logging ModifiedBy and ModifiedDate values when @StrictUserContext = 0 during update and delete operations
- Added an option in pAutoAuditAll to exclude all tables from specified schemas
- Changed the sort order for the _RowHistory UDF from AuditDate,RowVersion to RowVersion,AuditDate

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

Reviews for this release

     
Fantastic - been using the old version successfully for about a year.. originally the only issue i had was the rowhistory function which was very slow to generate but now its fast as anything! fantastic job! this tool is a great alternative to those who do not have sql enterprise and change tracking.
by Tiger333 on Nov 29, 2013 at 8:40 AM
Thank you for the great review! You can now download version 3.30 and take advantage of the new _TableRecovery point in time data retrieval table-valued UDF and a handful of other useful features. John
Reply by JohnSigouin (Coordinator) on Dec 4, 2013 at 3:22 PM