Data Browser - Viewing Site  Sector 23 Code Bank Logged in as:  Guest  




           


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


 Comments:
 > Guest 8/18/2014 10:56:05 PM
looks good!