1: Scroll to the bottom and get the third script first. It's a function, you'll need it for the other two procs. Run it in your company database.
2: This next proc (CreateAuditScripts2) creates a SCRIPT, that creates a TABLE, that allows you to pick the columns that you're going to audit.
There are three params that you'll need to edit, they're right at the top: SourceTableName (the table that you're auditing), AuditDB (optional, allows you to specify a different database to run your audits into), and TableDatabse (this is the database that the audit table resides in).
Note that if you're auditing tables in many company databases and you're using a separate database for your audits, you only need this once per table
-- =============================================
--
-- =============================================
-- updated 12/10/2010
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_CreateAuditScripts2'
AND type = 'P')
DROP PROCEDURE sp_CreateAuditScripts2
GO
CREATE PROCEDURE sp_CreateAuditScripts2
@sourcetablename varchar(50),
@TableDatabase VARCHAR(20),
@AuditDB VARCHAR(20)
as
declare @desttablename varchar(50)
IF @TableDatabase <> @AuditDB BEGIN
SET @desttablename = @TableDatabase + '_' + @sourcetablename + 'AUDIT'
END ELSE BEGIN
SET @desttablename = @sourcetablename + 'AUDIT'
END
declare @fieldName varchar(500)
declare @fieldType varchar(50)
DECLARE @OUTPUT varchar(max)
set @OUTPUT = ''
Print 'use ' + @AuditDB
--create the setup table
PRINT 'GO'
print 'set nocount on '
Print 'IF not EXISTS( SELECT NAME'
Print ' FROM sysobjects'
Print ' WHERE name = N' + char(39) + @sourcetablename + 'AUDITSETUP' + char(39)
Print ' AND type = ' + char(39) + 'U' + char(39) + ') begin '
print ''
declare curFields CURSOR for
select c.COLUMN_NAME,
dbo.ddf_columnType(data_type,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE) as fieldType
from INFORMATION_SCHEMA.COLUMNS c
where c.TABLE_NAME = @sourcetablename
Print ' CREATE TABLE ' + @sourcetablename + 'AUDITSETUP ('
Print ' ' + 'RowID int identity, '
Print ' ' + 'FieldName varchar(256), '
Print ' ' + 'Active bit) '
PRINT ''
OPEN curFields
FETCH NEXT FROM curFields INTO @fieldName, @fieldType
WHILE @@FETCH_STATUS = 0 BEGIN
if @fieldType <> 'text' and @fieldtype <> 'timestamp' begin
print ' insert into ' + @SOURCEtablename + 'AUDITSETUP (fieldname,Active) values ( ' + char(39) + @fieldname + char(39) + ',1)'
end
FETCH NEXT FROM curFields INTO @fieldName, @fieldType
END
CLOSE curFields
DEALLOCATE curFields
print 'end'
print 'go'
go
3: This script creates a SCRIPT that creates the table that we're going to run our audit data into
*** NOTE: when the script runs, if the target table (i.e.: if you're auditing IV00101, this would be the IV00101AUDIT table) already exists it will be backed up and renamed when this script is run. That allows you to change the columns that are audited and not lose data
It also create three triggers (UPDATE, INSERT, DELETE) on the target table that run the data in.
Note that the triggers use the SQL EXCEPT keyword in the UPDATE trigger. This keeps duplicate lines from being inserted into the audit table. Dynamics GP often update the data but doesn't change it. This is a big deal, it gets rid of more than half of the rows, it saves a lot of space and time.
-- =============================================
--
-- =============================================
-- updated 12/10/2010
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'sp_CreateAuditScripts3'
AND type = 'P')
DROP PROCEDURE sp_CreateAuditScripts3
GO
CREATE PROCEDURE sp_CreateAuditScripts3
@sourcetablename varchar(50),
@TableDatabase VARCHAR(20),
@AuditDB VARCHAR(20)
as
set nocount on
DECLARE @desttablename varchar(50)
IF @TableDatabase <> @AuditDB BEGIN
SET @desttablename = @TableDatabase + '_' + @sourcetablename + 'AUDIT'
END ELSE BEGIN
SET @desttablename = @sourcetablename + 'AUDIT'
END
declare @fieldName varchar(500)
declare @fieldType varchar(50)
DECLARE @OUTPUT varchar(max)
set @OUTPUT = ''
-------------------------
-- CREATE THE AUDIT TABLE
-------------------------
-- drop the table if it exists
Print 'use ' + @AuditDB
PRINT 'GO'
Print 'IF EXISTS( SELECT NAME'
Print ' FROM sysobjects'
Print ' WHERE name = N' + char(39) + @desttablename + char(39)
Print ' AND type = ' + char(39) + 'U' + char(39) + ')'
Print ' exec sp_rename ' + char(39) + @desttablename + char(39) + ',' + formatmessage('%s_%s',@desttablename,format(getdate(),'yyyyMMdd_hhmm'))
print 'go'
Print 'use ' + @TableDatabase
print 'go'
-- drop the trigger if it exists
Print 'IF EXISTS( SELECT NAME'
Print ' FROM sysobjects'
Print ' WHERE name = N' + char(39) + 't_4P_' + @sourcetablename + 'AUDIT_INS' + char(39)
Print ' AND type = ' + char(39) + 'TR' + char(39) + ')'
Print ' DROP TRIGGER ' + 't_4P_' + @sourcetablename + 'AUDIT_INS'
print 'go'
-- drop the trigger if it exists
Print 'IF EXISTS( SELECT NAME'
Print ' FROM sysobjects'
Print ' WHERE name = N' + char(39) + 't_4P_' + @sourcetablename + 'AUDIT_DEL' + char(39)
Print ' AND type = ' + char(39) + 'TR' + char(39) + ')'
Print ' DROP TRIGGER ' + 't_4P_' + @sourcetablename + 'AUDIT_DEL'
print 'go'
-- drop the trigger if it exists
Print 'IF EXISTS( SELECT NAME'
Print ' FROM sysobjects'
Print ' WHERE name = N' + char(39) + 't_4P_' + @sourcetablename + 'AUDIT_UPD' + char(39)
Print ' AND type = ' + char(39) + 'TR' + char(39) + ')'
Print ' DROP TRIGGER ' + 't_4P_' + @sourcetablename + 'AUDIT_UPD'
print 'go'
print ''
Print 'use ' + @AuditDB
DECLARE @T table (fieldname varchar(256))
insert into @t exec ('select fieldname from ' + @AUDITDB + '..' + @sourcetablename + 'auditsetup where active = 1')
--create the audit table
declare curFields CURSOR for
select c.COLUMN_NAME,
dbo.ddf_columnType(data_type,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE) as fieldType
from INFORMATION_SCHEMA.COLUMNS c
join @t t on t.fieldname = c.COLUMN_NAME
where c.TABLE_NAME = @sourcetablename
--sp_CreateAuditScripts3
Print 'CREATE TABLE ' + @desttablename + ' ('
Print ' ' + 'dtLogged datetime, '
Print ' ' + 'LogID int identity, '
Print ' ' + 'LogUserID varchar(256), '
Print ' ' + 'vchrLineType varchar(50), '
OPEN curFields
FETCH NEXT FROM curFields INTO @fieldName, @fieldType
WHILE @@FETCH_STATUS = 0 BEGIN
if @fieldType <> 'text' and @fieldtype <> 'timestamp' begin
set @OUTPUT = @OUTPUT + ' ' + @fieldName + ' ' + @fieldType + ', ' + CHAR(13) + CHAR(10)
end
FETCH NEXT FROM curFields INTO @fieldName, @fieldType
END
CLOSE curFields
DEALLOCATE curFields
Print left(@output, len(@output) - 3)
Print ')'
print 'go'
Print ''
print ''
print ''
print ''
Print 'use ' + @TableDatabase
print 'go'
Print 'CREATE TRIGGER t_4P_' + @sourcetablename + 'AUDIT_INS'
Print 'ON ' + @sourcetablename
Print 'FOR INSERT'
Print 'AS'
Print 'BEGIN'
declare @vchrFieldList as varchar(8000)
set @vchrFieldList = ''
----------------------------
-- CREATE THE INSERT TRIGGER
----------------------------
declare curFields CURSOR for
select c.COLUMN_NAME,
dbo.ddf_columnType(data_type,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE) as fieldType
from INFORMATION_SCHEMA.COLUMNS c
join @t t on t.fieldname = c.COLUMN_NAME
where c.TABLE_NAME = @sourcetablename
OPEN curFields
FETCH NEXT FROM curFields INTO @fieldName, @fieldType
WHILE @@FETCH_STATUS = 0 BEGIN
if @fieldType <> 'text' and @fieldtype <> 'timestamp' begin
set @vchrFieldList = @vchrFieldList + @fieldName + ', '
end
FETCH NEXT FROM curFields INTO @fieldName, @fieldType
END
CLOSE curFields
DEALLOCATE curFields
set @vchrFieldList = left(@vchrFieldList, len(@vchrFieldList) - 1)
print 'IF TRIGGER_NESTLEVEL() > 1 return'
print ''
Print 'INSERT INTO ' + @AuditDB + '..' + @desttablename + ' (' + @vchrFieldList + ', dtLogged, vchrLineType, LogUserID)'
Print ' SELECT ' + @vchrFieldList + ', ' + 'getdate()' + ', ' + QUOTENAME('INSERT',CHAR(39)) + ', ORIGINAL_LOGIN()'
Print ' FROM inserted'
Print 'END'
print 'go'
print ''
print ''
print ''
Print ''
-----------------------------
-- CREATE THE DELETED TRIGGER
-----------------------------
Print 'CREATE TRIGGER t_4P_' + @sourcetablename + 'AUDIT_DEL'
Print 'ON ' + @sourcetablename
Print 'FOR DELETE'
Print 'AS'
Print 'BEGIN'
set @vchrFieldList = ''
declare curFields CURSOR for
select c.COLUMN_NAME,
dbo.ddf_columnType(data_type,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE) as fieldType
from INFORMATION_SCHEMA.COLUMNS c
join @t t on t.fieldname = c.COLUMN_NAME
where c.TABLE_NAME = @sourcetablename
OPEN curFields
FETCH NEXT FROM curFields
INTO @fieldName, @fieldType
WHILE @@FETCH_STATUS = 0
BEGIN
if @fieldType <> 'text' and @fieldtype <> 'timestamp' begin
set @vchrFieldList = @vchrFieldList + @fieldName + ', '
end
FETCH NEXT FROM curFields INTO @fieldName, @fieldType
END
CLOSE curFields
DEALLOCATE curFields
set @vchrFieldList = left(@vchrFieldList, len(@vchrFieldList) - 1)
print 'IF TRIGGER_NESTLEVEL() > 1 return'
print ''
Print 'INSERT INTO ' + @AuditDB + '..' + @desttablename + ' (' + @vchrFieldList + ', dtLogged, vchrLineType,LogUserID)'
Print ' SELECT ' + @vchrFieldList + ', ' + 'getdate()' + ', ' + QUOTENAME('DELETE',CHAR(39)) + ', ORIGINAL_LOGIN()'
Print ' FROM deleted'
Print 'END'
print 'go'
print ''
print ''
print ''
Print ''
----------------------------
-- CREATE THE UPDATE TRIGGER
----------------------------
Print 'CREATE TRIGGER t_4P_' + @sourcetablename + 'AUDIT_UPD'
Print 'ON ' + @sourcetablename
Print 'FOR UPDATE'
Print 'AS'
Print 'BEGIN'
set @vchrFieldList = ''
declare curFields CURSOR for
select c.COLUMN_NAME,
dbo.ddf_columnType(data_type,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE) as fieldType
from INFORMATION_SCHEMA.COLUMNS c
join @t t on t.fieldname = c.COLUMN_NAME
where c.TABLE_NAME = @sourcetablename
OPEN curFields
FETCH NEXT FROM curFields INTO @fieldName, @fieldType
WHILE @@FETCH_STATUS = 0 BEGIN
if @fieldType <> 'text' and @fieldtype <> 'timestamp' begin
set @vchrFieldList = @vchrFieldList + @fieldName + ', '
end
FETCH NEXT FROM curFields INTO @fieldName, @fieldType
END
CLOSE curFields
DEALLOCATE curFields
set @vchrFieldList = left(@vchrFieldList, len(@vchrFieldList) - 1)
print 'IF TRIGGER_NESTLEVEL() > 1 return'
print ''
Print 'INSERT INTO ' + @AuditDB + '..' + @desttablename + ' (' + @vchrFieldList + ', dtLogged, vchrLineType, LogUserID)'
Print ' SELECT' + space(4 + len( @AuditDB + '..' + @desttablename)) + @vchrFieldList + ', ' + 'getdate()' + ', ' + + QUOTENAME('UPDATE INS',CHAR(39)) + ', ORIGINAL_LOGIN()'
Print ' FROM inserted'
print ' except'
Print ' SELECT' + space(4 + len( @AuditDB + '..' + @desttablename)) + @vchrFieldList + ', ' + 'getdate()' + ', ' + + QUOTENAME('UPDATE INS',CHAR(39)) + ', ORIGINAL_LOGIN()'
Print ' FROM deleted'
Print 'END'
print 'go'
-- sp_CreateAuditScripts3
go
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'ddf_columnType')
DROP FUNCTION ddf_columnType
GO
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'ddf_columnType')
DROP FUNCTION ddf_columnType
GO
CREATE FUNCTION ddf_columnType
(@ColumnType as nvarchar(256),@CHARACTER_MAXIMUM_LENGTH int, @intPrecision int , @intscale int)
-- select dbo.ddf_columnType('image',2147483647,null,null)
RETURNS varchar(20)
AS
BEGIN
declare @out varchar(20)
select @out =
case
when @ColumnType = 'binary' then 'binary(' + convert(varchar(4),@CHARACTER_MAXIMUM_LENGTH) + ')'
when @ColumnType = 'bit' then 'bit'
when @ColumnType = 'char' then 'char(' + convert(varchar(4),@CHARACTER_MAXIMUM_LENGTH) + ')'
when @ColumnType = 'date' then 'date'
when @ColumnType = 'datetime' then 'datetime'
when @ColumnType = 'decimal' then 'decimal(' + convert(varchar(4),@intPrecision) + ',' + convert(varchar(4),@intscale) + ')'
when @ColumnType = 'float' then 'float'
WHEN @ColumnType = 'int' then 'int'
when @ColumnType = 'image' then 'image'
when @ColumnType = 'money' then 'money'
when @ColumnType = 'numeric' then 'numeric(' + convert(varchar(4),@intPrecision) + ',' + convert(varchar(4),@intscale) + ')'
when @ColumnType = 'nvar' then 'nvar(' + convert(varchar(4),@CHARACTER_MAXIMUM_LENGTH) + ')'
when @ColumnType = 'nvarchar' then 'nvarchar(' + convert(varchar(4),@CHARACTER_MAXIMUM_LENGTH) + ')'
when @ColumnType = 'smalldatetime' then 'smalldatetime'
when @ColumnType = 'smallint' then 'smallint'
when @ColumnType = 'text' then 'text'
when @ColumnType = 'time' then 'time'
when @ColumnType = 'timestamp' then 'timestamp'
when @ColumnType = 'tinyint' then 'tinyint'
when @ColumnType = 'uniqueidentifier' then 'uniqueidentifier'
when @ColumnType = 'varbinary' then 'varbinary(' + CASE WHEN @CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' else CONVERT(varchar(4),@CHARACTER_MAXIMUM_LENGTH) end + ')'
when @ColumnType = 'varchar' and @CHARACTER_MAXIMUM_LENGTH = -1 then 'Varchar(MAX)'
when @ColumnType = 'varchar' then 'varchar(' + convert(varchar(4),@CHARACTER_MAXIMUM_LENGTH) + ')'
else 'unk - ' + convert(varchar(20),@ColumnType)
end
return @out
END
GO