AutoAudit 3.30
Note: The contents of this page is included at the top of the setup script.

----
--Please read completely and test on your database. --
--AutoAudit makes several changes to your tables. --
--Adding AutoAudit to your tables will impact performance. --
--Set the variables below to your requirements before executing.--
----

set nocount on
--USE AutoAudit -- edit for your database


--*******************************************************
-- VARIABLE DECLARATIONS
--*******************************************************
declare @AuditSchema varchar(50),
@ViewSchema varchar(50),
@Version varchar(5),
@OptimizeForAudit bit,
@DetailedMigrationCheck bit,
@CreatedColumnName sysname,
@CreatedByColumnName sysname,
@ModifiedColumnName sysname,
@ModifiedByColumnName sysname,
@RowVersionColumnName sysname,
@RebuildTriggersAfterInstall bit,
@WithLogFlag bit,
@DateStyle varchar(3),
@ViewPrefix varchar(10),
@UDFPrefix varchar(10),
@RowHistoryViewSuffix varchar(20),
@DeletedViewSuffix varchar(20),
@RowHistoryFunctionSuffix varchar(20),
@TableRecoveryFunctionSuffix varchar(20)


--*******************************************************
-- VARIABLE INITIALIZATION
--*******************************************************
Set @AuditSchema = 'Audit' --This is the schema to use for the AutoAudit objects. Edit for your database

Set @ViewSchema = '<TableSchema>' --This is the schema to use for the AutoAudit base table views. Edit for your database.
--<TableSchema> = the RowHistory and Deleted views have the same schema as the base table.

Set @Version = '3.30' --leave this unless you are making changes to this script

Set @OptimizeForAudit = 0 --@OptimizeForAudit = 0 creates an index to speed up views
--(and slows down AutoAudit), @OptimizeForAudit = 1 keeps AutoAudit 10% faster
--but the reporting views are slower.

Set @RebuildTriggersAfterInstall = 1 --@RebuildTriggersAfterInstall = 1 launches pAutoAuditRebuildAll after
--this script has completed and AutoAudit has been updated
--@RebuildTriggersAfterInstall = 0 runs this script to update AutoAudit but does NOT
--execute pAutoAuditRebuildAll. This may cause problems with your existing AutoAudit
--triggers views and UDFs.

Set @DetailedMigrationCheck = 0 --This is only applicable if you are upgrading your AutoAudit environment
--from version 2.00h to 3.20. The detailed verification could take several minutes to
--complete.
--0 = quick check with rowcount only, 1 = detailed record verification

Set @WithLogFlag = 0 --This flag determines if the "With Log" function is included in the raierror
--statements or not
--this is added because some DBA's may not have rights to write to the Windows log
--0 = exclude "with log", 1 = include "with log"

--set DDL column names
--*** make sure these DO NOT require quotename() (no spaces, special characters etc.)
--*** IF YOU ARE UPGRADING FROM AUTOAUDIT 2.X set the column names to Created, CreatedBy, Modified, ModifiedBy and RowVersion
-- otherwise the upgrade process will add new columns.
-- YOU CAN CHANGE THE NAMES LATER IN THE BASE TABLES AND THE AUTOAUDITSETTINGS TABLE.
Set @CreatedColumnName = 'AutoAudit_CreatedDate'
Set @CreatedByColumnName = 'AutoAudit_CreatedBy'
Set @ModifiedColumnName = 'AutoAudit_ModifiedDate'
Set @ModifiedByColumnName = 'AutoAudit_ModifiedBy'
Set @RowVersionColumnName = 'AutoAudit_RowVersion'

Set @DateStyle = '121' --this variable identifies the date style you wish to use when inserting data into
--the AuditHeader table. It is recommended you only use a style that provides full
--datetime precision with century. These are the tested and allowed choices.
-- 113 : 26 Nov 2013 13:20:54:553
-- 121 : 2013-11-26 13:22:55.170

--Set object prefixes and suffixes
Set @ViewPrefix = 'v' --User configurable - sets the PREFIX for RowHistory, Deleted views
Set @UDFPrefix = '' --User configurable - sets the PREFIX for RowHistory, TableRecovery functions
Set @RowHistoryViewSuffix = 'RowHistory' --User configurable - sets the suffix for "RowHistory" views
Set @DeletedViewSuffix = 'Deleted' --User configurable - sets the suffix for "Deleted" views
Set @RowHistoryFunctionSuffix = 'RowHistory' --User configurable - sets the suffix for "RowHistory" functions
Set @TableRecoveryFunctionSuffix = 'TableRecovery' --User configurable - sets the suffix for "TableRecovery" functions

--*******************************************************
-- END OF VARIABLE INITIALIZATION
--*******************************************************



/*
PLEASE READ NOTES COMPLETELY AND TEST ON YOUR DATABASE.

AUTOAUDIT MAKES SEVERAL CHANGES TO YOUR TABLES.

------------------------------
AutoAudit script
for SQL Server 2005, 2008, 2008R2, 2012
(c) 2007-2013 Paul Nielsen Consulting, inc.
www.sqlserverbible.com
AutoAudit.codeplex.com
Created by Paul Nielsen
Coded by Paul Nielsen and John Sigouin

December 2013
Version 3.30

------------------------------
executing this script will add the following
objects to your database:

Tables:
- <AuditSchema>.AuditHeader (new for 3.00)
- <AuditSchema>.AuditDetail (new for 3.00)
- <AuditSchema>.AuditHeaderArchive (new for 3.00)
- <AuditSchema>.AuditDetailArchive (new for 3.00)
- <AuditSchema>.AuditSettings (new for 3.00)
- <AuditSchema>.AuditBaseTables (new for 3.00)
- <AuditSchema>.AuditAllExclusions (new for 3.00)
- <AuditSchema>.SchemaAudit

Stored Procedures:
- <AuditSchema>.pAutoAudit
- <AuditSchema>.pAutoAutitDrop
- <AuditSchema>.pAutoAuditAll
- <AuditSchema>.pAutoAuditDropAll
- <AuditSchema>.pAutoAuditArchive (new for 3.00)
- <AuditSchema>.pAutoAuditRebuild (new for 3.00)
- <AuditSchema>.pAutoAuditRebuildAll (new for 3.00)

Views:
- <AuditSchema>.vAudit view (new for 3.00)
- <AuditSchema>.vAuditArchive view (new for 3.00)
- <AuditSchema>.vAuditAll view (new for 3.00)
- <AuditSchema>.vAuditHeaderAll view (new for 3.00)
- <AuditSchema>.vAuditDetailAll view (new for 3.00)

Database DDL Trigger:
- SchemaAuditDDLTrigger DDL Trigger
(on database for DDLDATABASELEVEL_EVENTS)


------------------------------
  • ***************************************************************************
Important: The 2.00h Audit table is being replaced by a AuditHeader and
AuditDetail table in version 3.x.
If you are currently using AutoAudit version 2.00h, running this
script will automatically create the new tables and migrate all
of your existing Audit data into the AuditHeader and AuditDetail
tables.
Also, all of your existing AutoAudit base table triggers will be
rebuild such that at the end of this installation, the system
will continue to audit as it used to but save the data to the
new tables.
Your current Audit table will be renamed to LegacyAudit_Migrated
and your current SchemaAudit table will be renamed to
dbo.LegacySchemaAudit_Migrated.
After verifying the installation and migration of your existing
Audit data to the new table structure, you should drop the
LegacyAuditMigrated and LegacySchemaAuditMigrated tables.
The new vAudit view created during the installation produces a
recordset identical to what is stored in your current Audit table.

****************************************************************
I am confident the upgrade process works correctly, but because
of possible differences in your installation compared to the
2.00h baseline installation, it is strongly recommended you
backup your database before running this script.
****************************************************************
***************************************************************************

  • Features (default behaviors):
Code-gens triggers to records all inserts, updates, and deletes
into a common generic audit table structure.

on insert: Records insert event in Audit tables (AuditHeader and AuditDetail)
including who made the insert, when, from what application and workstation.
The row's Created and CreatedBy columns also
reflect the user context.

on update: Records update events in the Audit tables including
who, when, from where, and the before and after values.
The row's Modified and ModifiedBy columns also store the basic
audit data. The update also increments the row's RowVersion column.

On delete: All the final values are written to the audit tables
while this permits undeleting rows, it is performance intensive
when deleting a large number of rows on a wide table.

------------------------------
Limitations:

Does not audit changes of columns of these data types:
text, ntext, image, geography, xml, binary, varbinary, timestamp,
rowversion

Adding AutoAudit triggers to a table will impact performance,
potentially doubling or tripling the normal DML execution times.
The width of the table increases the impact of the AutoAudit
triggers during updates.


AutoAudit database object description
----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.AuditHeader Table (new for 3.00)

This table is inserted with one row everytime one record is inserted,
updated or deleted in a table that has been setup to use the
AutoAudit system.

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.AuditDetail Table (new for 3.00)

This table is related to AuditHeader and is inserted with one row
for each column that is changed during an insert or update
operation and for each column during a delete operation.

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.AuditHeaderArchive Table (new for 3.00)

This table contains all the rows that originated in the AuditHeader
table but that have been selected to be archived based on the
archival timeframes processed by the
<AuditSchema>.pAutoAuditArchive procedure.


----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.AuditDetailArchive Table (new for 3.00)

This table contains all the rows that originated in the AuditDetail
table but that have been selected to be archived based on the
archival timeframes processed by the
<AuditSchema>.pAutoAuditArchive procedure.

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.SchemaAudit Table (modified for 3.00)

This table contains one row for each database DDL event that is
captured by the SchemaAuditDDLTrigger database trigger.

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.AuditSettings Table (new for 3.00)

This table contains a small number of rows that are used to persist
some important settings and parameters that are needed by the
AutoAudit system.

Important:
After running this script to install AutoAudit, please review
the User configurable settings and configure them to your
preferences.

Here's a list of entries in the AuditSettings table:
SettingName: AuditSchema
SettingValue: Audit (default)
AdditionalInfo: System setting added by AutoAudit installation
SQL script. Do not change manually in the table.

SettingName: Schema for RowHistory TableRecovery and _Deleted objects
SettingValue: <TableSchema> (default)
AdditionalInfo: User configurable - Schema AutoAudit uses for
RowHistory, TableRecovery and _Deleted objects.
Valid entries can be an existing schema or <TableSchema>.
The default is <TableSchema>. When <TableSchema>
is used, the schema of the AutoAudit views for
with match the schema of each base table.

SettingName: Version
SettingValue: 3.00 (current value)
AdditionalInfo: System setting added by AutoAudit installation
SQL script. Do not change manually in the table.

SettingName: SchemaAuditDDLTrigger Enabled Flag
SettingValue: 1 (default)
AdditionalInfo: User configurable - Immediate change. No action
required. 0 = DDL trigger disabled, 1 = DDL
trigger enabled.

SettingName: Archive Audit data older than (days)
SettingValue: 30 (default)
AdditionalInfo: User configurable - Immediate change. No action
required. Audit data older than this number of
days will be moved to the archive tables when the
pAutoAuditArchive stored procedure is executed.

SettingName: Delete Audit data older than (days)
SettingValue: 365 (default)
AdditionalInfo: User configurable - Immediate change. No action
required. Audit data older than this number of
days will be deleted permanently when the
pAutoAuditArchive stored procedure is executed.

SettingName: RowHistory View Scope
SettingValue: Active (default)
AdditionalInfo: User configurable - Must execute pAutoAuditRebuild(All)
or pAutoAudit(All) to apply change. Determines
source of data when _RowHistory views are created.
Valid entries are: "Active", "Archive", "All".

SettingName: Deleted View Scope
SettingValue: Active (default)
AdditionalInfo: User configurable - Must execute pAutoAuditRebuild(All)
or pAutoAudit(All) to apply change. Determines
source of data when _Deleted views are created.
Valid entries are: "Active", "Archive", "All".

SettingName: Default _RowHistory view Creation Flag
SettingValue: 1 (default)
AdditionalInfo: User configurable
0 = _RowHistory view is not created,
1 = _RowHistory view is created.

SettingName: Default _RowHistory function Creation Flag
SettingValue: 1 (default)
AdditionalInfo: User configurable
0 = _RowHistory function is not created,
1 = _RowHistory function is created.

SettingName: Default _TableRecovery function Creation Flag
SettingValue: 1 (default)
AdditionalInfo: User configurable
0 = _TableRecovery function is not created,
1 = _TableRecovery function is created.

SettingName: Default _Deleted view Creation Flag
SettingValue: 1 (default)
AdditionalInfo: User configurable
0 = _Deleted view is not created,
1 = _Deleted view is created.

SettingName: Launch pAutoAuditRebuild from SchemaAuditDDLTrigger Enabled Flag
SettingValue: 1 (default)
AdditionalInfo: System setting added by AutoAudit installation
SQL script. Do not change manually in the table.

SettingName: Audit Trigger Debug Flag
SettingValue: 0 (default)
AdditionalInfo: User configurable
0 = Debug information (The trigger name and nest level) in returned
by the AutoAudit Insert, Update and Delete triggers.
1 = Debug information is not returned

SettingName: Add Extended Properties Flag
SettingValue: 1 (default)
AdditionalInfo: User configurable
0 = Extended properties are not added.
1 = Extended properties are not added on DDL columns under the
MS_Decription name

SettingName: CreatedColumnName
SettingValue: AutoAudit_Created (default)
AdditionalInfo: User Configurable - Sets the column name of the column that is added
to the base tables to save the "record creation date" entry by AutoAudit
when "@BaseTableDDL = 1" is set in the execution of pAutoAudit.

SettingName: CreatedByColumnName
SettingValue: AutoAudit_CreatedBy (default)
AdditionalInfo: User Configurable - Sets the column name of the column that is added
to the base tables to save the "record created by" entry by AutoAudit
when "@BaseTableDDL = 1" is set in the execution of pAutoAudit.

SettingName: ModifiedColumnName
SettingValue: AutoAudit_Modified (default)
AdditionalInfo: User Configurable - Sets the column name of the column that is added
to the base tables to save the "record last modifocation date" entry
by AutoAudit when "@BaseTableDDL = 1" is set in the execution of pAutoAudit.

SettingName: ModifiedByColumnName
SettingValue: AutoAudit_ModifiedBy (default)
AdditionalInfo: User Configurable - Sets the column name of the column that is added
to the base tables to save the "record last modified by" entry by AutoAudit
when "@BaseTableDDL = 1" is set in the execution of pAutoAudit.

SettingName: RowVersionColumnName
SettingValue: AutoAudit_RowVersion (default)
AdditionalInfo: User Configurable - Sets the column name of the column that is added
to the base tables to save the "record verion number" entry by AutoAudit
when "@BaseTableDDL = 1" is set in the execution of pAutoAudit.

SettingName: ViewPrefix
SettingValue: v (default)
AdditionalInfo: User configurable (default = "v") - Must execute pAutoAuditRebuild(All) or
pAutoAudit(All) to apply change. Sets the prefix to use for the _RowHistory
and _Deleted views.

SettingName: DateStyle
SettingValue: 121 (default)
AdditionalInfo: System setting added by AutoAudit installation SQL script. Do not change
manually in the table. You can re-run the AutoAudit installation script to
change this setting.

SettingName: UDFPrefix
SettingValue: '' (default)
AdditionalInfo: User configurable (default = "") - Must execute pAutoAuditRebuild(All) or
pAutoAudit(All) to apply change. Sets the prefix to use for the _RowHistory
and _TableRecovery views.

SettingName: RowHistoryViewSuffix
SettingValue: _RowHistory (default)
AdditionalInfo: User configurable (default = "_RowHistory") - Must execute
pAutoAuditRebuild(All) or pAutoAudit(All) to apply change. Sets the suffix
to use for the _RowHistory views.

SettingName: DeletedViewSuffix
SettingValue: _Deleted (default)
AdditionalInfo: User configurable (default = "_Deleted") - Must execute pAutoAuditRebuild(All)
or pAutoAudit(All) to apply change. Sets the suffix to use for the _Deleted
views.

SettingName: RowHistoryFunctionSuffix
SettingValue: _RowHistory (default)
AdditionalInfo: User configurable (default = "_RowHistory") - Must execute
pAutoAuditRebuild(All) or pAutoAudit(All) to apply change. Sets the suffix to
use for the _RowHistory functions.

SettingName: TableRecoveryFunctionSuffix
SettingValue: _TableRecovery (default)
AdditionalInfo: User configurable (default = "_TableRecovery") - Must execute
pAutoAuditRebuild(All) or pAutoAudit(All) to apply change. Sets the suffix to
use for the _TableRecovery functions.

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.AuditBaseTables Table (new for 3.00)

This table contains one row for each of the base tables that was
setup to use the AutoAudit system. The data in this table can
be useful for a DBA who wants to review which base tables are
setup for Auto
Changes to the AutoAudit configuration for each table can be done in
this table.
The pAutoAuditRebuild stored procedure is dependant on the data in
this table to work.

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.AuditAllExclusions Table (new for 3.00)

This table contains a user-defined list or base tables to exclude
when the pAutoAuditAll stored procedure is executed.

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.vAudit view (new for 3.00)

This view retrieves data from AuditHeader and AuditDetail to produce
a recordset with the same structure as the Audit table had in
Version 2.00h.

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.vAuditArchive view (new for 3.00)

This view retrieves data from AuditHeaderArchive and
AuditDetailArchive to produce a recordset with the same structure
as the Audit table had in Version 2.00h.

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.vAuditAll view (new for 3.00)

This view does a Union All of the data from the vAudit and
vAuditArchive tables. In essence this is a view to all the data
contained in the AutoAudit (Active and Archive) tables.

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.vAuditHeaderAll view (new for 3.00)

This view does a Union All of the data from the AuditHeader and
AuditHeaderArchive tables. In essence this is a view to all the
header data contained in the AutoAudit (Active and Archive)
tables.

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.vAuditDetailAll view (new for 3.00)

This view does a Union All of the data from the AuditdDetail and
AuditDetailArchive tables. In essence this is a view to all the
detail data contained in the AutoAudit (Active and Archive)
tables.

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.pAutoAudit Procedure

applies AutoAudit to a single table

parameters:
@SchemaName sysname - the schema of the table (default = 'dbo')
@TableName sysname - the name of the table (required)
(sysname is NVARCHAR(128))

@StrictUserContext BIT (default = 1)
@LogSQL BIT (Default = 0)
@BaseTableDDL BIT (Default = 0)
@LogInsert TINYINT (Default = 2)
@LogUpdate TINYINT (Default = 2)
@LogDelete TINYINT (Default = 2)

---
pAutoAudit will make the following changes:

add columns: Created, CreatedBy, Modified, ModifiedBy,
and RowVersion if @BaseTableDDL = 1
add triggers: tablenameAuditInsert, tablenameAuditUpdate,
tablenameAuditDelete
add views: <AuditViewSchema>.vtablename_Deleted,
<AuditViewSchema>.vtablename_RowHistory
add function: <AuditViewSchema>.tablename_RowHistory,
<AuditViewSchema>.tablename_TableRecovery

---
Options:

@StrictUserContext determines how user context columns are set
(user - CreatedBy and ModifiedBy, audit time - Created
and Modified)
1 = (default) user context set by server login - suser_sname()
and server time (GetDate())
0 = user context default to server values, but can be determined
by DML and are nullable.

When using @StrictUserContext = 0:
Insert: an insert DML statement can insert into the Created
and CreatedBy columns.
Update: an update DML statement can freely update the Created,
CreatedBy, Modified, and ModifiedBy columns.
Delete: delete DML statements do not include dml columns, so when
the @StrictUserContext is set to 0, the previous modified
and modified values are captured into the audit trail table.
To record the correct delete user and datetime, first touch
(update) the row's Modified and/or ModifiedBy columns.

For most applications leaving @StrictUserContext on is approriate.
Turning @StrictUserContext off is useful for two use cases:
1 - applications that manage their own user security and log into
SQL Server using a common security context. These applications
can pass the user name to AutoAudit by inserting into the base
table's CreatedBy column or updating the base table's Modified
column.
2 - when importing data from a previous database that already has
legacy audit data.

The StrictUserContext = 0 requires the BaseTableDDL option enabled,
since the CreatedBy and ModifiedBy columns are used to pass in
the user context.

---
@LogSQL determines if the SQL batch that fired the event is logged

1 = the SQL Batch is logged in the SQLStatement column
0 = (default) the SQL Batch is not logged

SQL logging is useful for debugging, however, it can severely** BlOaT the
audit log, so it should be normally set off (or the storage team will laugh
at you when your 6 Gb database grows to 115Gb in a week ;-)

---
@BaseTableDDL determines if the Created, CreatedBy, Modified, ModifiedBy
and RowVersion columns are added to the base tables
0 = make no changes to the base tables
1 = (default) add the Created, CreatedBy, Modified, ModifiedBy, and
RowVersion columns to the base tables

Adding the Created, Modified, and RowVersion columns is appropriate for
most tables. However, some third party databases do not allow modifying
the base table.

---
@LogInsert determines how much is logged to the audit trail on an insert
event.
0 - Nothing is logged to the audit trail tables. This is useful for
importing data and avoiding a false insert event in the Audit table.
When not loggin the insert, you can still get the inserted datetime
from the Created column and the update event will have the old value.
1 - The insert event is written to the AuditHeader table
2 - (default) The AuditHeader is written and all columns are written
to the AuditDetail table.

@LogUpdate determines how much is logged to the audit trail on an insert
event.
0 - Nothing is logged to the audit trail tables. *** Use this option with
caution! You will not be able to re-create your data at a point in
time with this setting.
1 - The update event is written to the AuditHeader table. *** Use this option
with caution! You will not be able to re-create your data at a point in
time with this setting.
2 - (default) The AuditHeader is written and all updated columns are
written to the AuditDetail table.

@LogDelete determines how much is logged to the audit trail on an insert
event.
0 - Nothing is logged to the audit trail tables. *** Use this option with
caution! You will not be able to re-create your data at a point in
time with this setting.
1 - The insert event is written to the AuditHeader table. *** Use this option
with caution! You will not be able to re-create your data at a point in
time with this setting.
2 - (default) The AuditHeader is written and all columns are written
to the AuditDetail table.

Regardless of the @LogInsert and @LogUpdate setting, the Created, CreatedBy,
Modified, ModifiedBy, and RowVersion columns on the base table are always
set if AutoAudit was added to the table with option @BaseTableDDL = 1.

---

To change the options for a table, simply re-exec the pAutoAudit proc
with the required options to re-generate the triggers for the table.
Alternatively, you can update the AutoAudit settings changes on a
table by table basis in the <AuditSchema>.AuditBaseTables table
and then execute <AuditSchema>.pAutoAuditRebuild or
<AuditSchema>.pAutoAuditRebuildAll.

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.pAutoAuditAll Procedure

Executes pAutoAudit for every basetable except for the tables listed
in the <AuditSchema>.AuditAllExclusions table.

parameters:
@StrictUserContext BIT (default = 1)
@LogSQL BIT (Default = 0)
@BaseTableDDL BIT (Default = 0)
@LogInsert TINYINT (Default = 2)
@LogUpdate TINYINT (Default = 2)
@LogDelete TINYINT (Default = 2)


----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.pAutoAuditDrop Procedure

Removes columns, triggers, views, and function
created by pAutoAudit for a single table.

parameters:
@SchemaName sysname - the schema of the table (default = 'dbo')
@TableName sysname - the name of the table (required)
(sysname is NVARCHAR(128))
@DropBaseTableDDLColumns BIT (Default = 1)
0 = keeps the base table DDL columns
1 = (default) drops the base table DDL columns
@DropBaseTableTriggers BIT (Default = 1)
0 = keeps the base table AutoAudit Triggers
1 = (default) drops the base table AutoAudit Triggers
@DropBaseTableViews BIT (Default = 1)
0 = keeps the base table AutoAudit views and function
1 = (default) drops the base table AutoAudit views and function

It does not remove the audit tables or SchemaAudit
trigger or table created when this script is executed
in a database.

If your intention is to keep the AutoAudit triggers but drop the
DDL columns, you will also have to use <AuditSchema>.pAutoAuditRebuild

----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.pAutoAuditDropAll Procedure

Drops selected components of Auto
Optionally executes pAutoAuditDrop for every basetable using the
default options.

parameters:
@DropAuditTables BIT (Default = 0)
0 = (default) keeps the AutoAudit tables
1 = drops all the AutoAudit tables
@DropAuditViews BIT (Default = 0)
0 = (default) keeps the AutoAudit views
1 = drops all the AutoAudit views
@DropAuditSPs BIT (Default = 0)
0 = (default) keeps the AutoAudit SP's
1 = drops all the AutoAudit SP's
@DropAuditDDLTriggers BIT (Default = 0)
0 = (default) keeps the AutoAudit DDL Trigger
1 = drops the AutoAudit DDL Trigger
@DropBaseTableDDLColumns BIT (Default = 0)
0 = (default) keeps the base table DDL columns
1 = drops the base table DDL columns
from each base table
@DropBaseTableTriggers BIT (Default = 0)
0 = (default) keeps the base table AutoAudit Triggers
1 = drops the base table AutoAudit Triggers
from each base table
@DropBaseTableViews BIT (Default = 0)
0 = (default) keeps the base table AutoAudit views and function
1 = drops the base table AutoAudit views and function
@ConfirmAllDrop varchar(10) (Default = 'no')
'no' = (default) Does not proceed with the AllDrop SP
'yes' = proceeds with the AllDrop SP

Important:
If @DropAuditTables=1 then @DropAuditViews, @DropBaseTableTriggers,
@DropAuditSPs, @DropBaseTableViews and @DropAuditDDLTriggers
are forced to 1

When @DropAuditTables, @DropAuditViews or @DropAuditSPs
are flaged as 1, pAutoAuditDropAll removes AutoAudit components
from the database. Depending on the options the schema audit
DDL trigger and table, and the Audit tables will be removed.


----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.pAutoAuditRebuild Procedure (new for 3.00)

Drops and re-creates the Audit base table triggers, views and
function. The re-created components use the settings from the
<AuditSchema>.AuditBaseTables table for the specified based table.

This AuditBaseTables table and pAutoAuditRebuild sp can be very
useful if columns have been added/modified in a base table. If
you need to make AutoAudit settings changes to one or more
tables all you need to do is change entries in the AuditBaseTables
table. If a column is added to a base table and needs to be included
in the AuditDetail list of columns, add the column name to the
ColumnNames value for that base table before rebuilding the AutoAudit
objects. For example if you want to remove SQL statement loging on
all base tables, you can update that flag for all records and simply
execute the pAutoAuditRebuild or pAutoAuditRebuildAll procedure.

parameters:
@SchemaName sysname - the schema of the table (default = 'dbo')
@TableName sysname - the name of the table (required)

Important:
If "Launch pAutoAuditRebuild from SchemaAuditDDLTrigger Enabled Flag"
is set to 1 in the <AuditSchema>.AuditSettings table, when the
SchemaAuditDDLTrigger database DDL trigger fires due to a base
table schema change, SchemaAuditDDLTrigger makes an automagic
call to pAutoAuditRebuild after a ALTER_TABLE event.


----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.pAutoAuditRebuildAll Procedure (new for 3.00)

Executes pAutoAuditRebuild for every basetable that has an entry
in the <AuditSchema>.AuditBaseTables table.

parameters:
<none>


----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.pAutoAuditArchive Procedure

Moves a portion of the data from the AuditHeader and AuditDetail
tables to the AuditHeaderArchive and AuditDetailArchive tables
and/or deletes Audit data permanently.
This stored procedure should be executed on a regular basis
(with SSA) to keep the live Audit tables to a reasonable size and
performance level.

parameters:
@ArchiveAfterNumberOfDays int = -1
The number of days after which the audit data will be moved
to the Archive table. If -1 is entered, then the setting from
the <AuditSchema>.AuditSettings table will be used.
@DeleteAfterNumberOfDays int = -1
The number of days after which the audit data will be permanently
deleted from the archive (or active) Audit tables. If -1 is
entered, then the setting from the <AuditSchema>.AuditSettings
table will be used.
@KeepLastEntry bit = 1
0 = keeps the base table AutoAudit views and function
1 = (default) The last Audit entry for each primary key is not
archived (even if it should based on dates) to ensure a
sequential RowVersion is produced when logging future changes.

Important:
It is recommended to leave @KeepLastEntry bit = 1 when you did not
add the DDL columns to the base table otherwise you may
end-up with multiple Audit entries with the same RowVersion.

@DeleteAfterNumberOfDays must be >= @ArchiveAfterNumberOfDays


----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.pAutoAuditSetTriggerState Procedure (new for 3.02)

Enables or disables AutoAudit triggers at the SQL Server level.

This is different from the EnabledFlag entry in the AuditBaseTables
tables. The pAutoAuditSetTriggerState SP enables or disables the
triggers at the SQL Server level where the EnabledFlag entry in the
AuditBaseTables table keeps the triggers enabled but makes the
AutoAudit exit before logging the Audit event.

parameters:
@SchemaName sysname - the schema of the table (default = 'dbo')
@TableName sysname - the name of the table (required)
@InsertEnabledFlag BIT = 1 (Default = 1)
1 = the insert trigger is enabled
0 = the insert trigger is disabled
@UpdateEnabledFlag BIT = 1 (Default = 1)
1 = the update trigger is enabled
0 = the update trigger is disabled
@DeleteEnabledFlag BIT = 1 (Default = 1)
1 = the delete trigger is enabled
0 = the delete trigger is disabled


----------------------------------------------------------------
----------------------------------------------------------------
  • <AuditSchema>.pAutoAuditSetTriggerStateAll Procedure (new for 3.02)

Executes pAutoAuditSetTriggerState for every basetable that has an entry
in the <AuditSchema>.AuditBaseTables table.

parameters:
@InsertEnabledFlag BIT = 1 (Default = 1)
1 = the insert trigger is enabled
0 = the insert trigger is disabled
@UpdateEnabledFlag BIT = 1 (Default = 1)
1 = the update trigger is enabled
0 = the update trigger is disabled
@DeleteEnabledFlag BIT = 1 (Default = 1)
1 = the delete trigger is enabled
0 = the delete trigger is disabled


-------------------------------------------------------------
-------------------------------------------------------------
Development Change History

-------------------------------
version 1.01 - Jan 15, 2007
added RowVersion column, incremented by the modified trigger
cleaned up how the tablename is written to the tablename column
added delete trigger, which just writes the table, pk, and operation ('d') to the audit table
changed Column to ColumnName
-------------------------------
version 1.02 - Jan 16, 2007
fixed bug: Duplicate Columns. databases with user-defined types was causing the user-defined types to show up as system types.
added code gen to create table_Deleted view that returns all deleted rows for the table

-------------------------------
version 1.03 - Jan 16, 2007
converted from cursor to Multiple Assignment Variable for building of for-each-column code
added Created, Modified, and deleted columns to _Deleted view

-------------------------------
version 1.04 - Jan 18, 2007
minor clean-up on _Deleted view. Removed extra Primary Key Column.

-------------------------------
version 1.05 - Jan 18, 2007
changed from writing just the delete bit to writing the whole row.
modified _Deleted view to return RowVersion

-------------------------------
version 1.06 - Jan 30, 2007
added host_name to audit trail
improved modified trigger run-away recursive trigger detection
added basic error-trapping

-------------------------------
version 1.07 - Feb 6, 2007
idea from Gary Lail - don't log inserts, only updates
added pRollbackAudit procedure
changed all stored procedure names to pName
CREATE PROC usp AS SELECT OBJECT_NAME( @@PROCID )

-------------------------------
version 1.08 - June 25, 2008
case sensitive cleanup
defaults named properly
defaults and columns dropped in AutoAuditDrop proc

-------------------------------
version 1.09 - Oct 15, 2008
fixed @tablename bug in AutoAuditDrop
changed audit time from GetDate() to inserted.Created and inserted.Modified to keep these times in synch
changed from 'data type in()' to 'data type not in (xml, varbinary, image, text)'
added support for hierarchyID tracking (from Cast to Convert)
added check: Table must have PK
added check: PK must not be HierarchyID
added RowVersion to dbo.Audit, and insert/update/delete procs
added RowHistory Table Valued Function
added SchemaAudit table and database trigger
SchemaAuditDDLTrigger also fires pAutoAudit for Alter_Table events for tables with AutoAudit

-------------------------------
version 1.09a - Oct 18, 2008
fixed hard-coded path in _RowHistory dynamic SQL builder code
changed _RowHistory values not updated from 0 to null

-------------------------------
version 1.09b - Oct 23, 2008
changed SchemaSchema and .Object to allow nulls for events that do not have schema.object

-------------------------------
version 1.10 - Jan 24, 2010

issue: NULL Updates that don't actually update anything
were still updating the Modified column
and incrementing the RowVersion
fix:
eliminated the Modified trigger
moved updating the Modified Column and incrementing the version number to the Update Trigger

moved update of Created col to insert trigger
added Modified and RowVersion col to Updated

improved error reporting slightly

added capture of user's SQL Statement/Batch

added SET ARITHABORT ON : bug and fix reported by pjl on CodePlex on Jun 15 2009 at 9:35 AM

added CreatedBy and ModifiedBy columns. If names passed to tables, then this value captured for Audit trail.

-------------------------------
version 1.10e - Mar 20, 2010

cleaned up documentation
cleaned up sysname data type for parameters
added .dbo as default to schema parameter
added drop of audit tables and ddl trigger to pAutoAuditDropAll

-------------------------------
version 2.00 - April 5, 2010

Added StrictUserContext Option
@StrictUserContext = 1
if 1 then blocks DML inserting or updating Created, CreatedBy, Modified, ModifiedBy
if 0 then permits DML setting of Created, CreatedBy, Modified, ModifiedBy

-------------------------------
version 2.00c - April 26, 2010
increased Application column to 128 to allow for SSIS package names

-------------------------------
version 2.00d - May, 2010
bug fixes for StrictSUer Context

-------------------------------
version 2.00e July, August 2010

more bug fixes for StrictSUer Context

-- Get Modified working tweak CreatedBy no updated logic

added @LogSQL option
added @BaseTableDDL option

-------------------------------
version 2.00f July, August 2010
added @LogInsert option

-------------------------------
version 2.00g August, 2010
removed CreatedBy, ModifiedBy from RowHistory function

added sql_variant to the list of not audited data types
it was giving the RowHistory function a conumption

Added brackets around primary key column name in RowHistory function (reported by Anthony - SQLDownUnder)

-------------------------------
version 2.00g August, 2010
fixed drop of SchemaTable in pAutoAuditDropAll (reported by Calvin Jones)
changed StrictUserContext ModifiedBy column constraint to NOT NULL (reported by Calvin Jones)
removed variable initialization for SQL Server 2005 compatability (reported by Calvin Jones)
removed SchemaAudit from pAutoAuditAll and pAutoAuditDropAll (Reproted by jeffcj)


-------------------------------
version 2.00i Sept, 2010
changed SYSNAME to sysname for case sensitive collation
added code to block recursive runs of the trigger

-------------------------------
version 2.00j Oct 7, 2010
RowVersion function incorrectlty reports initial null values and the first non-null value for initial row


-------------------------------
version 3.00 January, 2012 (coded by John Sigouin)
1.Added code to implement row version in the Audit table when the
base table DDL audit columns do not exist
2.Updated the _Deleted view so that it shows all the deletes for each PK
(in case a row is inserted,deleted,inserted,deleted...)
3.Added a column to the _Deleted view to flag latest delete for each PK
4.Modified the Update trigger to handle updates of the PK when a single
row is updated (even if it's bad to update a PK, sometimes it can happen)
5.Updated the _RowHistory function to make it return the full history even
after a row is deleted and re-inserted
DONE! RowHistory() requires an existing row for the current row. Therefore,
it fails when viewing the RowHistory() of a deleted row. (reported by sathish4000)

6.Added code to drop CreatedBydf and ModifiedBydf defaults in pAutoAuditDrop
Added code to drop CreatedBy and ModifiedBy audit columns in pAutoAuditDrop
7.Changed SchemaObject column datatype from varchar(50) to sysname
8.Changed the PrimaryKey column datatype from varchar(25) to varchar(36)
to accomodate guid's
9.Added the SchemaAuditID column to the SchemaAudit table and set it as the
primary key. There was no Primary Key on that table previously.
10.Added parameter to this script to allow user to specify a schema for the
AutoAudit tables, SPs, views etc (default is Audit)
- the Deleted views and the RowHistory objects can also be created
in the specified schema (which could be the same as the AutoAudit schema),
the same schema as the base table with the <TableSchema> keyword or any
other existing schema as specified.
DONE! Schema is a parameter to this script - Change all code to Audit schema (suggested by Calvin Jones)

11.Created a new Audit table called AuditSettings to store AutoAudit
configuration and default settings.
12.Normalized the Audit table by creating a AuditHeader table and a AuditDetail table.
13.Create the vAudit view joining the AuditHeader and AuditDetail tables so that
the vAudit view looks just like the former Audit table.
14.Used a unpivot query to insert the AuditDetail records rather than the current
individual insert for each column.
15.The normalization and unpivot modification produced a performance improvement
of over 70% on inserts.
16.Re-designed the _deleted view for optimization with the new table structure.
17.Created a new _RowHistory view to return the row history through a view rather
than a Table-Valued UDF.
18.Redesigned the RowHistory Table-Valued UDF to use the new RowHistory view for
backward compatibility.
19.Updated the pAutoAuditDrop SP to remove the new objects.
20.Created one new index for performance on AuditHeader.PrimaryKey
21.The pAutoAudit SP parameter behaviour has been modified to accomodate the
AuditHeader and AuditDetail table structure. The level of detail logged during
insert, update and delete operations can be controlled.
FOR INSERTS: (this was in 2.00h)
- When @LogInsert = 2, the AuditInsert trigger writes an entry to the
AuditHeader table and also inserts to the AuditDetail table.
This logs the creation of the record and each column value inserted.
- When @LogInsert = 1, the AuditInsert trigger writes an entry to the
AuditHeader table but does not insert anything to the AuditDetail table.
This logs the creation of the record only.
- When @LogInsert = 0, the insert event is not logged at all in the Audit tables.
The insert trigger is not created.

FOR UPDATES:
******************************************************************************
WARNING: BE AWARE THAT IF YOU SET @LogUpdate TO ANYTHING OTHER THAN 2 YOU WILL
NOT BE ABLE TO RE-CREATE THE DATA IN YOUR TABLE FOR RECOVERY OF
ACCIDENTAL MODIFICATIONS.
******************************************************************************
- When @LogUpdate = 2, the AuditUpdate trigger writes an entry to the
AuditHeader table and also inserts to the AuditDetail table.
This logs the update of the record and each of the updated column values.
- When @LogUpdate = 1, the AuditUpdate trigger writes an entry to the
AuditHeader table but does not insert anything to the AuditDetail table.
This logs the update of the record only.
- When @LogUpdate = 0, the update event is not logged at all in the Audit tables.
The update trigger is not created unless @BaseTableDDL = 1.
DONE! LogUpdate Option to turn off Update Trigger ??

FOR DELETES:
******************************************************************************
WARNING: BE AWARE THAT IF YOU SET @LogDelete TO ANYTHING OTHER THAN 2 YOU WILL
NOT BE ABLE TO RE-CREATE THE DATA IN YOUR TABLE FOR RECOVERY OF
ACCIDENTAL MODIFICATIONS.
******************************************************************************
- When @LogDelete = 2,the AuditDelete trigger writes an entry to the
AuditHeader table and also inserts to the AuditDetail table.
This logs the deletion of the record and each column value before the delete.
- When @LogDelete = 1,the AuditDelete trigger writes an entry to the
AuditHeader table but does not insert anything to the AuditDetail table.
This logs the deletion of the record only.
- When @LogDelete = 0,the delete event is not logged at all in the Audit tables.
The delete trigger is not created.

22.Added parameters to pAutoAuditAll SP to match the ones in pAutoAudit except
the table schema and name.
23.Changed the call to pAutoAudit from pAutoAuditAll to forward the specified
parameters instead of always using defaults.
24.Added parameters to pAutoAuditDropAll SP to pick exactly which AutoAudit
components are being dropped.
@DropAuditTables bit=1, --0 = don't drop audit tables, 1 = drop audit tables
-- if @DropAuditTables=1 then @DropAuditViews,@DropBaseTableTriggers,
-- @DropAuditSPs and @DropAuditDDLTriggers defaults to 1
@DropAuditViews bit=1, --0 = don't drop audit views, 1 = drop audit views
@DropAuditSPs bit = 1, --0 = don't drop audit SP's, 1 = drop audit SP's
@DropAuditDDLTriggers bit = 1, --0 = don't drop audit database DDL trigger, 1 = drop audit database DDL trigger
@DropBaseTableDDLColumns bit = 1, --0 = don't drop Base Table DDL Columns, 1 = drop Base Table DDL Columns
@DropBaseTableTriggers bit = 1, --0 = don't drop audit triggers on tables, 1 = drop audit triggers on tables
@DropBaseTableViews bit=1 --0 = don't drop BaseTable views, 1 = drop BaseTable views

25.Changed the call to pAutoAuditDrop from pAutoAuditDropAll to forward the specified
parameters instead of always using defaults.
26.Added print statements to sp's to inform the administrator exactly what is happening.
27.Added Quotename(...) delimiters all over. AutoAudit will work even if you have a table named
$chem@.This is my test crazy table name! and a column named $ Amount. :-)
DONE! QuoteName() (suggested by Rob Farley)

28.The base table schema does not have to be the same as the schema used for the AutoAudit tables.
29.Corrected a bug with the Update trigger where if a statement was executed with
an exec or execute_sql prefix (eg. exec (N'Update mytable set Column1 = 'newvalue'
where Column1 = 'oldvalue'') compared to Update mytable set Column1 = 'newvalue'
where Column1 = 'oldvalue'. With the exec or execute_sql syntax, the RowVersion
column was not being incremented in the base table.
30.Added a configuration switch to the AuditSettings table to enable/disable the
Database DDL Trigger.
DONE! Option Switch for DDLSchemaLog

31.Created a new table called AuditBaseTables.
-Whenever a table is setup for AutoAudit, a record with the specific AutoAudit
for this base table is created in the AutoAuditBaseTables table. Subsequently,
AutoAudit functionality can be enabled/disabled on a table by table basis by
toggling the EnabledFlag column value. (ie. may want to disable before inserting
updating or deleting millions or records).
-If the auditing settings (StrictUserContext,LogSQL,BaseTableDDL,LogInsert) needs
to be changed on a base table(s), that can be done in this table. After the flags
have been changed, execute the pAutoAuditRebuild SP to implement the new settings.
DONE! in the AuditBaseTables table instead of extended properties
set options in extended properties (suggested by Calvin Jones)

EXEC sp_addextendedproperty
@name = N'StrictUserContext', @value = 1,
@level0type = N'Schema', @level0name = Juror,
@level1type = N'Table', @level1name = tblJuror,
@level2type = N'TRIGGER', @level2name = trgJurorAudit_Delete;

SELECT * FROM ::fnlistextendedproperty('StrictUserContext', 'SCHEMA','Juror', 'TABLE','tblJuror', 'TRIGGER','trgJurorAuditDelete')
WHERE value=1

32.Created a new SP called pAutoAuditRebuild to re-create the AutoAudit trigger on a table
if the table's AutoAudit settings have been changed in the AuditBaseTables table or if
columns have been added/modified in a base table.
32.Created a new SP called pAutoAuditRebuildAll to execute the pAutoAuditRebuild sp for
each of the tables that exist in the AuditBaseTables table.
23.Changed SchemaAuditDDLTrigger trigger to call pAutoAuditRebuild after a ALTER_TABLE event.
34.Forced exclusion of AuditHeader, AuditDetail, SchemaAudit tables in pAutoAuditAll SP.
35.Created a new table called AuditAllExclusions. All tables listed in this table will be
excluded from the pAutoAuditAll and pAutoAuditRebuildAll SP's. You can use this to
customize your own exclusions.

Exclude MS tables and Audit schema table from AutoAuditAll (suggested by Calvin Jones)
DONE! exclusion of AuditHeader, AuditDetail, SchemaAudit tables and added a table
to specifify any other exclusions.

36.Created tables to store AutoAudit archive data. The tables are called AuditHeaderArchive
and AuditDetailArchive. Added indexes to the Archive tables.
DONE! Archiving of Audit table
Move to Archive table proc set up as Job
Indexing of Archive table
View to Union Audit and AuditArchive tables

37.Created the vAuditArchive view. Identical to the vAudit view but for the Archive tables.
38.Created the vAuditAll view which unions the vAudit (active) and vAuditArchive views.
39.Created the vAuditHeaderAll view which unions the AuditHeader and AuditHeaderArchive tables.
40.Created the vAuditDetailAll view which unions the AuditDetail and AuditDetailArchive tables.
41.Created a Stored Procedure <AuditSchema>.pAutoAuditArchive to move data from the active
Audit tables to the archive tables and to delete the archive data past the retention
period. The retention periods are user defined in the AuditSettings table. Even if
an Audit record should be archived based on its dates and the @KeepLastEntry parameter
is set to 1 (default), it will remain in the active tables
if it is the last audit entry for a particuler TableName-PrimaryKey combination to retain
row version sequence for future Audit entries. The call to this SP should be setup as a
SQL Server Agent scheduled job to run daily or weekly.
42.Added settings "RowHistory View Scope" and "Deleted View Scope" in the AuditSettins table
that determines the scope of the RowHistory and Deleted views. Valid entries are:
"Active", "Archive", "All". This setting is used when the views are created, it is not
dynamically verified everytime the view is used for performance reasons.
"All" includes "Active" and "Archive". Default is "Active"
43.Added a section to this script to automagically transfer the existing legacy single Audit
table data to the new tables in the case where this is an upgrade to a version 2.00h installation.
44.Added Audit Settings to flag if the RowHistory view, RowHistory function and _Deleted
view are created when AutoAudit triggers are added to a table.
45.Added a seperate variable to identify the schema to use for the AutoAudit base table views.
I had set it up originally to use the same schema as the Audit tables but I think some DBA's
might want to match the schema of the data table or use a different schema altogether. The
options for this parameter are: The first option is a specific schema for example
"Audit" or "dbo" or any other schema. The second option is "<TableSchema>" which means
match the view schema to the table schema.
46.Use contextinfo to prevent the update trigger from firing when the _insert trigger writes
the Created, Modified and RowVersion data to the base table. Same process for preventing the
database DDL trigger from firing when the Created, Modified and RowVersion columns are added
to the base table.
47.Used Convert(varchar,<input>,113) instead of cast(<input> as varchar) when inserting data
into the AuditDetail table to maintain the full precision of datetime columns. The cast
function method in version 2.00h was saving the OldValue and NewValue date entries in this
format "Dec 15 2011 7:56PM" thereby loosing the seconds and milliseconds.
48.An optional index can be created to improve the performance of the delete and RowHistory
views by a factor of 10 on the AuditDetail.AuditHeaderID column. The downside is the
AutoAudit loging uses about 20% more I/O and is 10% slower. The script parameter
@OptimizeForAudit = 0 creates the index to speed up views (and slow down AutoAudit) and
@OptimizeForAudit = 1 keeps AutoAudit faster but the views and archiving of old Audit
data are slower. Set the parameter to your preference before running this script or
create/drop the index manually as you wish.
49.Restructured the AutoAudit Update trigger to correctly record the RowVersion when a query
updates multiple records but not all of the records are actually changed. For example:
Update Items set Status = 'Active'; If some of the records already had a Status='Active'
their RowVersion was being incremented in base tables where the BaseTableDDL columns were
added even if there was no actual change and no Audit data recorded.
Now, only the records that are actually changed have their RowVersion, Modified
and ModifiedBy updated.
50.Added the "Source" column in the RowHistory and Deleted views to indicate if the rows
comes from the "active" or "archive" Audit tables.


-------------------------------
version 3.01 January, 2012 (coded by John Sigouin)
1. Added the SysUser column to the RowHistory view and RowHistory() function.
DONE! Add SysUser to RowHistory (Requested by Patrick Jackman)

2. Added the DeletedBy (SysUser) column to the _Deleted view.
DONE! Add DeletedBy column to the v_Deleted view

3. Retested with Case Sensitive Collation using SQLLatin1GeneralCP1CS_AS database
collation. Made corrections to make it all work.
DONE! Must retest with Case Sensitive Collation

4. Added debug option to Audit triggers. When the "Audit Trigger Debug Flag" setting
is set to 1 (on) in the AuditSettings tables, The trigger name and nest level will
be returned.
The default setting is 0 (off).
DONE! Debug Option - when true includes print statements to report the execution of the trigger and the nest level

5. Added calls to sp_addextendedproperty when DDL columns are added to the base table
with the @BaseTableDDL = 1 option.
DONE! Add MS_Description extended property to columns added by AutoAudit (suggested by Calvin Jones)

6. Added a parameter to flag the creation of extended properties as 1=create,
0=do not create. The setting name is "Add Extended Properties Flag" in the AuditSettings table.
The default value is 1.

7. Added the following entries to the AuditAllExclusions tables.
- AuditAllExclusions
- AuditBaseTables
- AuditSettings
Setting up AutoAudit on these tables is OK but they are excluded by default. If you
want AutoAudit to be setup on these tables, simply remove the names from the
AuditAllExclusions table or use the pAutoAudit stored procedure to set them up individually.

8. Bug fix: Corrected the creation of the RowHistory table function to refer to @ViewSchema.vRowHistory
rather that @AuditSchema.v_RowHistory.

9. Added a call to pAutoAuditDrop in the Database DDL trigger when a table is dropped to
automatically drop the AutoAudit views that were related to that table.

10. Added code to delete AuditBaseTables record from pAutoAuditDrop when a table is dropped.

11. Added the option for users to rename the Created, CreatedBy, Modified, ModifiedBy, RowVersion
columns at the beginning of this script. Note: Column names must be standard SQL Server column
names that do not require to be processed by QUOTENAME.
DONE! User options to set DDL Column names (Created, CreatedBy, Modified, ModifiedBy, RowVersion) (Suggested by Neal Walters)


-------------------------------
version 3.02 February, 2012 (coded by John Sigouin)
1. Created stored procedure pAutoAuditSetTriggerState that enables/disabled the
insert, update and/or delete AutoAudit triggers for the specified base table.
This is different from the EnabledFlag in the AuditBaseTables table because the triggers
are enabled/disabled at the SQL Server level.
**********************************************************************************
WARNING: BE AWARE THAT IF YOU DISABLE AutoAudit TRIGGERS YOU WILL NOT HAVE ANY
RECORD OF DATA MANIPULATION EVENTS FOR THE UNDERLYING TABLE. DISABLING
TRIGGERS SHOULD ONLY BE DONE WHEN YOU ARE LOADING MASSIVE AMOUNTS OF DATA
INTO YOUR TABLE AND YOU PLAN ON USING YOUR STANDARD BACKUP STRATEGY TO
PROTECT YOUR DATA. MAKE SURE YOU RE-ENABLE THE AutoAudit TRIGGERS ONCE
YOUR ADMINISTRATIVE OPERATIONS ARE DONE.
**********************************************************************************


-------------------------------
version 3.20 January, 2013 (coded by John Sigouin)
1. Add the @WithLogFlag flag to this script
- This flag determines if the "With Log" function is included in the raiserror statements or not
- this is added because some DBA's may not have rights to write to the Windows log
- 0 = exclude "with log", 1 = include "with log"
2. Add capability for AutoAudit to handle tables with multi-column PK's. AutoAudit is now capable of
handling tables which have up to 5 columns used in the definition of the primary key.
- There is still a maximum width of 36 characters for each PK column
- The _RowHistory table-valued functions have a number of input parameters to match the number of
PK columns in the base table
3. Add the @ColumnNames optional parameter to pAutoAudit to specify a sub-set of the base tables
columns to include in the AuditDetail loging of changes.
- The columns must be listed in the following format 'column1,column2,...' where each column
name is enclosed in square brackets.
- The primary key column(s) do not need to be listed as they are always included.
- The default value for this parameter '<All>' indicates that all columns are to be included.
- The setting for this parameter is written to the AuditBaseTables table as used when AutoAudit
triggers are rebuilt.


-------------------------------
version 3.20a January, 2013 (coded by John Sigouin)
1. Added RowVersion column to list of included columns in the IXAuditHeaderPrimaryKey index
2. Added Application and SQLStatement columns to the RowHistory views and RowHistory table-valued
functions

-------------------------------
version 3.20b February, 2013 (coded by John Sigouin)
1. Reworked the _RowHistory table-valued functions to return full row-by-row details of the audit
events when the Logging level is set to 2 (full loging) for Insert, Update and Delete events.
When Insert Logging is set to 0 (none) or 1 (minimal), then that basic output (which is the
same as the _RowHistory views) isreturned.

-------------------------------
version 3.20d February, 2013 (coded by John Sigouin)
1. Fixed bug related to the _RowHistory creation script.
- missing "and si.indid = sik.indid" table join criteria
- handling of max (-1) column size
- fixed problem when PK was not at the beginning of table columns

version 3.20d April, 2013 (coded by John Sigouin)
1. Added SET ANSI_PADDING ON statement to SchemaAuditDDLTrigger trigger to resolve bug

-------------------------------
version 3.20e October, 2013 (coded by John Sigouin)
1. Set the Trigger order to run first for the AutoAudit insert triggers. This change is implemented
to process AutoAudit insert triggers correctly where there is another insert trigger in the table.
If that other insert trigger ends up updating or deleting the inserted trigger, all the steps will
be logged in the AuditDetail table.


-------------------------------
version 3.20f November, 2013 (coded by John Sigouin)
1. Added missing in _RowHistory UDF creation script. Suggested by patrikwiik
2. Added 'RolePermissions','sysdiagrams' and t.name not like 'aspnet_%' in list of excluded tables
in the pAutoAuditAll sp. Suggested by patrikwiik


-------------------------------
version 3.20g November, 2013 (coded by John Sigouin)
1. Corrected bug with the _RowHistory UDF where the function would fail when a non string nullable
column or string column narrower than 8 characters. (Submitted by Rolv)


-------------------------------
version 3.20h November, 2013 (coded by John Sigouin)
1. Modified the logic for logging ModifiedBy and ModifiedDate values when @StrictUserContext = 0
during an update operation with the following logic
- if values are updated to those columns with the user query, then those values are kept and
used in the AuditHeader table (columns AuditDate, SysUser)
- if NULL values are updated to those columns with the user query, then AutoAudit uses the
server time and the logged in user name in the base table and in the AuditHeader table
- if the values for the ModifiedBy and ModifiedDate are left unchanged with the update
statement, then the previous values will remain in the base table and be used again in the
AuditHeader table
2. Modified the logic for saving AuditDate and SysUser values in the AuditHeader table when
@StrictUserContext = 0 during a delete operation.
- In the previous versions, the delete trigger was using the
last entries for CreatedDate and CreatedBy in the base table as the source for the AuditDate
and SysUser entries in AuditHeader. This has been corrected to use the ModifiedDate and
ModifiedBy base table columns instead. Therefore, if you want to log the current client time
and login during a delete, you now have to update (touch operation) the ModifiedBy and
ModifiedDate base table columns just before doing your delete.
3. Changed the sort order for the _RowHistory UDF from AuditDate,RowVersion to RowVersion,AuditDate
4. Removed the @AuditSchema variable declaration and initialization from the SchemaAuditDDLTrigger
trigger because it was not used.
5. Added an option in pAutoAuditAll to exclude all tables from specific schemas as required. For
example, if you have tables in schema MySchema and you want them to be excluded from
AutoAudit trigger creation when you run pAutoAuditAll, you can add this exclusion row in the
AuditAllExclusions SchemaName = MySchema, TableName = <All>.
6. Added RowVersion to the list of column datatypes that are not supported by AutoAudit.
7. Added filter on primary key column datatypes that are not allowed: 'binary', 'varbinary',
'timestamp', 'rowversion'
8. Added SQL_Variant datatype columns back in as allowed datatypes for AutoAudit columns


-------------------------------
version 3.30 December, 2013 (coded by John Sigouin)
1. Added a new powerful feature to AutoAudit, the creation of the _TableRecovery table-valued UDF.
This UDF takes a date/time value as parameter and returns a recordset of the contents of the
table as it was at that point in time. This data can be used to recover accidently modified data,
populate reports etc.
2. Added the Comment column to the AuditAllExclusions table to optionally document the reason
for the exclusion. (suggested by rosacek)
3. Added a variable to configure the date style AutoAudit uses for entries in the AuditHeader and
AuditDetail tables
4. Added User option to set the prefix on AutoAudit views and UDF (suggested by rosasek)
5. Added User option to set the suffix on AutoAudit views and UDF (why not go all the way! :-) )
6. Changed the defaults for pAutoAuditDropAll to 0 instead of 1 and added a confirmation input
parameter as failsafe measures to prevent accidental removal of AutoAudit.
7. Added the LoginName column to the SchemaAudit table.
8. Added QUOTENAME delimiters around TableName entry in AuditHeader table entries. A script updates
existing entries in AuditHeader and AuditHeaderArchive.
9. Added the @DateStyle parameter that allows the user to select date style 113 or 121 for date
storage in the AuditHeader and AuditDetail tables.

Last edited Dec 4, 2013 at 3:50 PM by JohnSigouin, version 7

Comments

jonathan1234566 Oct 2, 2015 at 4:43 AM 
Hi anyone experience slow performance issues on autoaudit 3.30 ?
i facing slow performance issues and causing whole system time out on production server.

Hi have any one recommend what is the minimum SQL server specification to run this autoaudit 3.0 in enterprise system.

how to cover SQL server usage if a lots of user doing update in same timing.


Thanks,

GedP Mar 31, 2014 at 9:01 AM 
Stupendous work! Thank you very much.

rosacek Dec 6, 2013 at 7:49 AM 
John thanks for your job, really appreciate!
R.