Generic Audit of SQL Table Add the following trigger to insert/update to audit changes to fields. XXX is Table Name XXX requires column ModifiedBy and Modified, which should be updated on every insert/update. AuditLog is a table with columns: TableName, FieldName, RecordId, Value, Modified, ModifiedBy Add the following trigger to insert/update to audit changes to fields. XXX is Table Name If XXX doesn't include floats/decimals, you can remove the cast to varchar and it will perform better. GO /****** Object: Trigger [dbo].[XXXAuditTrigger] Script Date: 08/04/2014 13:39:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Amos Zoellner -- Create date: 8/18/2010 -- Description: Stores data in auditlog after insert/update -- ============================================= CREATE TRIGGER [dbo].[XXXAuditTrigger] ON [dbo].[XXX] FOR INSERT, UPDATE NOT FOR REPLICATION AS BEGIN -- temp vars DECLARE @ColumnName varchar(50) DECLARE @Sql varchar(max) -- make data available for inner query SELECT * INTO #INSERTED FROM INSERTED SELECT * INTO #DELETED FROM DELETED -- get list of updated column indexes DECLARE @ColumnsUpdated VARBINARY(100) SET @ColumnsUpdated = COLUMNS_UPDATED() -- get the list of updated field names (using indexes) DECLARE cur CURSOR FOR ( SELECT COLUMN_NAME AS Name FROM INFORMATION_SCHEMA.COLUMNS Field WHERE TABLE_NAME = 'XXX' AND COLUMN_NAME NOT IN ('ModifiedBy', 'Modified') AND sys.fn_IsBitSetInBitmask ( @ColumnsUpdated, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') ) <> 0 ) -- foreach updated field, insert it into the audit log OPEN cur FETCH NEXT FROM cur INTO @ColumnName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'INSERT INTO AuditLog (TableName, FieldName, RecordId, Value, Modified, ModifiedBy) SELECT ''XXX'', ''' + @ColumnName + ''', #INSERTED.Id, #INSERTED.'+ @ColumnName + ', isnull(#INSERTED.modified,getdate()), #INSERTED.modifiedby FROM #INSERTED LEFT JOIN #DELETED ON #INSERTED.Id = #DELETED.Id -- cast to varchar(max) since decimals do not work in the <> comparison here with isnull WHERE ISNULL(cast(#INSERTED.' + @ColumnName + ' as varchar(max)),'''') <> ISNULL(cast(#DELETED.' + @ColumnName + ' as varchar(max)), '''')' EXEC (@sql) FETCH NEXT FROM cur INTO @ColumnName END -- end while fetching -- dispose cursor CLOSE cur DEALLOCATE cur -- drop data DROP TABLE #INSERTED DROP TABLE #DELETED END GO
Created By: amos 8/4/2014 2:47:42 PM
|
|