You can use SQL triggers to capture changes to your WIN-911 configuration and dump them to an Audit table. The following SQL script will create a new table called Audit and create triggers to capture all INSERT, UPDATE, and DELETE actions. In the example below we are creating the triggers for the WIN-911 Dispatcher Source module [COMPUTERNAME-911.Default.Dispatcher]. You will need to apply these triggers to every WIN-911 module's database.
USE [COMPUTERNAME-911.Default.Dispatcher] GO IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit') CREATE TABLE Audit ( AuditID [int]IDENTITY(1,1) NOT NULL, Type char(1), TableName varchar(128), PrimaryKeyField varchar(1000), PrimaryKeyValue varchar(1000), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000), UpdateDate datetime DEFAULT (GetDate()), UserName varchar(128) ) GO DECLARE @sql varchar(8000), @TABLE_NAME sysname SET NOCOUNT ON SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' WHILE @TABLE_NAME IS NOT NULL BEGIN EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking') SELECT @sql = ' create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete as declare @bit int , @field int , @maxfield int , @char int , @fieldname varchar(128) , @TableName varchar(128) , @PKCols varchar(1000) , @sql varchar(2000), @UpdateDate varchar(21) , @UserName varchar(128) , @Type char(1) , @PKFieldSelect varchar(1000), @PKValueSelect varchar(1000) select @TableName = ''' + @TABLE_NAME+ ''' -- date and user select @UserName = system_user , @UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114) -- Action if exists (select * from inserted) if exists (select * from deleted) select @Type = ''U'' else select @Type = ''I'' else select @Type = ''D'' -- get list of columns select * into #ins from inserted select * into #del from deleted -- Get primary key columns for full outer join select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @TableName and CONSTRAINT_TYPE = ''PRIMARY KEY'' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME -- Get primary key fields select for insert select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + '''''''' from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @TableName and CONSTRAINT_TYPE = ''PRIMARY KEY'' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))'' from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = @TableName and CONSTRAINT_TYPE = ''PRIMARY KEY'' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME if @PKCols is null begin raiserror(''no PK on table %s'', 16, -1, @TableName) return end select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName while @field < @maxfield begin select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field select @bit = (@field - 1 )% 8 + 1 select @bit = power(2,@bit - 1) select @char = ((@field - 1) / 8) + 1 if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'') begin select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field select @sql = ''insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)'' select @sql = @sql + '' select '''''' + @Type + '''''''' select @sql = @sql + '','''''' + @TableName + '''''''' select @sql = @sql + '','' + @PKFieldSelect select @sql = @sql + '','' + @PKValueSelect select @sql = @sql + '','''''' + @fieldname + '''''''' select @sql = @sql + '',convert(varchar(1000),d.['' + @fieldname + ''])'' select @sql = @sql + '',convert(varchar(1000),i.['' + @fieldname + ''])'' select @sql = @sql + '','''''' + @UpdateDate + '''''''' select @sql = @sql + '','''''' + @UserName + '''''''' select @sql = @sql + '' from #ins i full outer join #del d'' select @sql = @sql + @PKCols select @sql = @sql + '' where (SELECT '''''' + @fieldname + '''''' from #ins) <> (SELECT '''''' + @fieldname + '''''' from #del)'' select @sql = @sql + '' or ((SELECT '''''' + @fieldname + '''''' from #ins) is null and (SELECT '''''' + @fieldname + '''''' from #del) is not null)'' select @sql = @sql + '' or ((SELECT '''''' + @fieldname + '''''' from #ins) is not null and (SELECT '''''' + @fieldname + '''''' from #del) is null)'' exec (@sql) end end ' SELECT @sql EXEC(@sql) SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' END