Introduction
There is an obvious need to implement an audit function in database applications. Having devised this solution I could not see a similar approach taken by anyone else, hence I am presenting here.
My audit tool implements the following features:
- an Audit table storing details of all insert, update and delete operations
- only data changes are captured, i.e. tool does not capture the details of the entire row on every data change
- data change operations are grouped in the Audit table, e.g. if 10 rows are updated each Audit row will have an identical Operation Ref
- an Audit Config table allows control over which tables and columns to apply the audit tool.
Using the code
The audit tool is implemented like so:
Any table implementing the audit tool will have three triggers. Each trigger will:
- store the inserted and deleted tables to a local temporary table;
- get some dynamic SQL from a
GetAuditSQL
function; - execute the SQL code (this generates the Audit data);
- delete the temporary tables.
The trigger SQL code is below:
ALTER TRIGGER [dbo].[Customer_DELETE_Audit] ON [dbo].[Customer] AFTER DELETE
AS
BEGIN
SET NOCOUNT ON
select * into #tmpDeleted from deleted;
declare @sql nvarchar(max);
select @sql = [audit].[GetAuditSQL]('dbo','Customer','delete','','#tmpDeleted');
if isnull(@sql,'') <> '' exec sp_executesql @sql;
drop table #tmpDeleted;
END
GO
ALTER TRIGGER [dbo].[Customer_INSERT_Audit] ON [dbo].[Customer] AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
select * into #tmpInserted from inserted;
declare @sql nvarchar(max);
select @sql = [audit].[GetAuditSQL]('dbo','Customer','insert','#tmpInserted','');
if isnull(@sql,'') <> '' exec sp_executesql @sql;
drop table #tmpInserted;
END
GO
ALTER TRIGGER [dbo].[Customer_UPDATE_Audit] ON [dbo].[Customer] AFTER UPDATE AS
BEGIN
SET NOCOUNT ON
select * into #tmpInserted from inserted;
select * into #tmpDeleted from deleted;
declare @sql nvarchar(max);
select @sql = [audit].[GetAuditSQL]('dbo','Customer','update','#tmpInserted','#tmpDeleted');
if isnull(@sql,'') <> '' exec sp_executesql @sql;
drop table #tmpInserted;
drop table #tmpDeleted;
END
GO
The GetAuditSQL
code will iterate over the columns for the given table in AuditConfig and generate code that:
- compares the deleted and inserted data (from the trigger) to see where changes exist;
- derive old and new values from the deleted and inserted data;
- insert rows into the Audit table.
The GetAuditSQL
code is below:
ALTER function [audit].[GetAuditSQL](
@Schemaname varchar(100),
@Tablename varchar(100),
@OperationType varchar(100),
@InsertedTableName varchar(100),
@DeletedTableName varchar(100)) returns nvarchar(max)
as
begin
declare @OpRef varchar(100) = isnull((select max(OperationRef)+1 FROM audit),1)
declare @retval nvarchar(max) = N''
if not exists (select 0 from AuditConfig where Tablename = @Tablename and EnableAudit = 1)
return @retval
set @retval = N'
insert into [audit].[Audit] ([Login],OperationRef,Schemaname,
Tablename,OperationType,ColumnName,RowID,OldValue,NewValue)
select SYSTEM_USER, ''' + @OpRef + ''',''' + @Schemaname + ''',''' +
@Tablename + ''',''' + @OperationType+ ''', * from
('
declare @AddUnion bit = 0;
declare @ColumnName varchar(100) = ''
declare ac cursor for select ColumnName from AuditConfig
where Tablename = @Tablename and EnableAudit = 1
open ac
fetch next from ac into @ColumnName
while @@FETCH_STATUS = 0
begin
if @AddUnion = 1 SET @retval = @retval + N' union '
if @OperationType = 'insert'
begin
SET @retval = @retval + N'
select ''' + @ColumnName + ''' ColName, i.ID, NULL OldValue,
convert(nvarchar(max),i.['+ @ColumnName +
']) NewValue from [' + @InsertedTableName + '] i'
end
if @OperationType = 'update'
begin
SET @retval = @retval + N'
select ''' + @ColumnName + ''' ColName, i.ID, convert(nvarchar(max),d.['+ @ColumnName + ']) OldValue,
convert(nvarchar(max),i.['+ @ColumnName + ']) NewValue from [' + @InsertedTableName + '] i JOIN [' +
@DeletedTableName + '] d ON i.ID = d.ID AND (i.['+ @ColumnName +
'] <> d.['+ @ColumnName + '] OR i.['+ @ColumnName +
'] IS NOT NULL AND d.['+ @ColumnName + '] IS NULL)'
end
if @OperationType = 'delete'
begin
SET @retval = @retval + N'
select ''' + @ColumnName + ''' ColName, i.ID,
convert(nvarchar(max),i.['+ @ColumnName + ']) OldValue,
NULL NewValue from [' + @DeletedTableName + '] i'
end
SET @AddUnion = 1
fetch next from ac into @ColumnName
end
close ac
deallocate ac
SET @retval = @retval + N') d'
return @retval
end
The script file attached to this article will set up the audit tool in it's entirety:
- Set up the Audit and AuditConfig tables
- Set up the GetAuditSQL function
- Set up two SPs (these can be called when new tables or columns are introduced to the database):
- to auto-populate the AuditConfig table with all table and column names;
- set up all triggers.
- Execute the two SPs, applied to a specific schema (dbo by default)
There are notes in the header that should be read before executing the script. Hope this is helpful!
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.