Data Change Tracking Solution for Auditing SQL Server

Bart Ducal

4.00/5 (1 vote)

29 Jan 2017CPOL2 min read

12.8K

Universal data change tracking solution for auditing SQL Server databases

Introduction

Full auditing solutions that track changes (INSERT, UPADTE and DELETE) in SQL Server tables and fields. It may be used as a simple but effective solution to track data changes in any SQL Server database. The solution does not have any special requirement on SQL Server side and is easy to implement.

Background

The solution requires the creation of three tables in SQL Server database:

LoggerField Settings - store settings for fields to be tracked for data changes
LoggerTableSettings - store settings for tables to be tracked for data changes
Logger - store tracked changes

All the magic is done by a trigger created on LoggerTableSettings table. The trigger dynamically generates final triggers on user tables. Yes, trigger creates trigger! Those final triggers insert auditing data in Logger table rows.

Using the Code

This is a fully functional demo script. It creates TESTLogger database with three required tables (mentioned above). At the end of the script, there is a piece of code that creates Demo table and sets some auditing. Finally, we do some INSERT, UPDATE and DELETE on Demo table and show the auditing results in Logger table.

SQL

Create DATABASE TESTLogger
go

use TESTLogger
go


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Logger](
    [TableName] [varchar](500) NULL,
    [FieldName] [varchar](500) NULL,
    [KeyValue] [varchar](200) NULL,
    [OldValue] [varchar](8000) NULL,
    [NewValue] [varchar](8000) NULL,
    [ModifyAction] [nvarchar](50) NULL,
    [ModifyDate] [datetime] NULL,
    [ModifyUser] [varchar](500) NULL,
    [ModifyExtraData1] [varchar](500) NULL,
    [ModifyExtraData2] [varchar](500) NULL,
    [ModifyExtraData3] [varchar](500) NULL,
    [ModifyExtraData4] [varchar](500) NULL,
    [ModifyExtraData5] [varchar](500) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[LoggerFieldSettings](
    [IDLoggerFieldSettings] [int] IDENTITY(1,1) NOT NULL,
    [TableName] [nvarchar](500) NULL,
    [FieldName] [nvarchar](500) NULL,
 CONSTRAINT [PK_LoggerSettings] PRIMARY KEY CLUSTERED 
(
    [IDLoggerFieldSettings] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[LoggerTableSettings](
    [IDLoggerTableSettings] [int] IDENTITY(1,1) NOT NULL,
    [TableName] [nvarchar](500) NULL,
    [LogInserts] [bit] NULL,
    [LogDeletes] [bit] NULL,
 CONSTRAINT [PK_LoggerTableSettings] PRIMARY KEY CLUSTERED 
(
    [IDLoggerTableSettings] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
go

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE TRIGGER [dbo].[Logger_Crete_Trigger] ON [dbo].[LoggerTableSettings] 
FOR INSERT, UPDATE, DELETE
AS

set quoted_identifier off
set nocount on

declare @TableName nvarchar(500)

declare @i int
select @i=count(*) from deleted
if @i>0 
begin
      select @TableName=TableName from deleted

      if exists (select * from dbo.sysobjects where id = _
      object_id(N'[Logger_Trigger_Unique_Name_0123456789_' + @TableName+']') _
      and OBJECTPROPERTY(id, N'IsTrigger') = 1)
      execute('drop trigger [Logger_Trigger_Unique_Name_0123456789_'+@TableName+']')
end

select @i=count(*) from inserted
if @i=0
      return

select @TableName=TableName from inserted

if exists (select * from dbo.sysobjects where id = _
   object_id(N'[Logger_Trigger_Unique_Name_0123456789_' + @TableName+']') _
   and OBJECTPROPERTY(id, N'IsTrigger') = 1)
execute('drop trigger [Logger_Trigger_Unique_Name_0123456789_'+@TableName+']')


if not exists (select * from dbo.sysobjects where id = object_id(@TableName) _
   and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      return

declare @fields nvarchar(4000)
declare @fname nvarchar(500)
declare @status int
set @fields='' 
set @fname=''

declare cf cursor for select name,[status] from syscolumns _
   where id=object_id(@TableName) and xtype not in (34,35,99) order by colid
open cf
FETCH NEXT FROM cf INTO @fname,@status
while @@FETCH_STATUS=0 
begin 
      if (@status=128)
            set @fname='convert(varchar(100),['+@fname+']) _
            [Logger Identity Column 1234567890 Unique Name _0987654321_], _
            convert(varchar(100),['+@fname+']) ['+@fname+']'
      else
            set @fname='['+@fname+']'
            
      if @fields='' 
            set @fields=@fname
      else 
            set @fields=@fields + ',' + @fname
FETCH NEXT FROM cf INTO @fname,@status
end 
close cf 
deallocate cf
if @fields=''
      return

declare @nr as nvarchar(1)
set @nr=char(13)

--klucz
declare @keycol varchar(100)
declare @keycols varchar(200)
declare @keycolsins varchar(200)
declare @keycolsdel varchar(200)
declare @ordpos int
set @keycol=''
set @keycols=''
set @keycolsins=''
set @keycolsdel=''
declare keys cursor for select kcu.COLUMN_NAME, kcu.ORDINAL_POSITION
  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
  join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
    on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
   and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
   and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
   and kcu.TABLE_NAME = tc.TABLE_NAME
where tc.CONSTRAINT_TYPE='PRIMARY KEY'
and kcu.TABLE_NAME=@TableName
order by 2 asc

open keys
fetch next from keys into @keycol,@ordpos
while @@fetch_status=0
begin

      if @keycols=''
            begin
                  set @keycols=@keycols+'convert(varchar(100),_
                  #t_inserted.'+@keycol+')' 
                  set @keycolsins=@keycolsins+'convert(varchar(100),_
                  #t_inserted1.'+@keycol+')' 
                  set @keycolsdel=@keycolsdel+'convert(varchar(100),_
                  #t_deleted1.'+@keycol+')' 
            end
      else
            begin
                  set @keycols=@keycols+'+'+'char(33)+_
                  convert(varchar(100),#t_inserted.'+@keycol+')'
                  set @keycolsins=@keycolsins+'+'+'char(33)+_
                      convert(varchar(100),#t_inserted1.'+@keycol+')'
                  set @keycolsdel=@keycolsdel+'+'+'char(33)+_
                      convert(varchar(100),#t_deleted1.'+@keycol+')' 
            end

fetch next from keys into @keycol,@ordpos
end

close keys
deallocate keys 
if @keycols=''
begin
      set @keycols='char(33)'
      set @keycolsins='char(33)'
      set @keycolsdel='char(33)'
end 
--koniec klucza


execute("CREATE TRIGGER [Logger_Trigger_Unique_Name_0123456789_" + _
        @TableName+"] ON ["+ @TableName +"] _
        FOR INSERT, UPDATE, DELETE  AS " +
"declare @TableName varchar(500) " +
"set @TableName='"+@TableName+"' "

+"declare @UserName varchar(500) "+ @nr
+"set @UserName=host_name() "+ @nr+ @nr

+"declare @separator varchar(5) "+ @nr
+"set  @separator=',' "+ @nr+ @nr

+"set nocount on "+ @nr+ @nr


+" declare @rc_inserted int "+ @nr
+"declare @rc_deleted int "+ @nr
+"declare @i int "+ @nr
+"select @rc_inserted=count(*) from inserted "+ @nr
+"select @rc_deleted=count(*) from deleted "+ @nr+ @nr

+"declare @cmd varchar(max) "+ @nr
+"declare @FieldName varchar(500) "+ @nr+ @nr
+"if @rc_inserted=0  and @rc_deleted>0 "
+"begin "+ @nr
+"    select @i=count(*) from LoggerTableSettings _
where LogDeletes=1 and TableName=@TableName "+ @nr
+"    if @i=0 "+ @nr
+"          return "+ @nr+ @nr

+"    select " + @fields + "  into #t_deleted1 from deleted "+ @nr
+"    set @cmd='' "+ @nr
+"    declare c cursor for select name from syscolumns where id=object_id(@TableName) _
              and xtype not in (34,35,99)   order by colid "+ @nr
+"    open c "+ @nr
+"    fetch next from c into @FieldName "+ @nr
+"    while @@FETCH_STATUS=0 "+ @nr
+"    begin "+ @nr
+"          if @cmd='' "+ @nr
+"                set @cmd='case when [' + _
@FieldName + '] is null then ' + _
                  '''' + '''' + ' _
                  else convert(varchar(8000),['+ @FieldName + ']) end '  "+ @nr
+"          else "+ @nr
+"                set @cmd=@cmd+ '+' +'''' + _
@separator + '''' + ' +_
                  case when [' + @FieldName + '] is null _
                  then ' + '''' + '''' + ' _
                  else convert(varchar(8000),['+ @FieldName + ']) end '  "+ @nr
+"          fetch next from c into @FieldName "+ @nr
+"    end "+ @nr
+"    close c "+ @nr
+"    deallocate c "+ @nr+ @nr

+"    set @cmd='insert into Logger(ModifyAction,TableName,_
         FieldName,KeyValue,OldValue,NewValue,ModifyDate,ModifyUser) _
         select ' +''''+'DELETE' + _
         '''' + ','+  '''' + _
         @TableName + '''' + ',' + 'null' + ',"+@keycolsdel+",_
              substring(' + @cmd + ',1,8000),null,' + _
              '''' + convert(varchar(50),getdate(),13) + _
              '''' + ',' + '''' + _
              @UserName + '''' +' from #t_deleted1' "+ @nr
+"    execute(@cmd) "+ @nr
+"    return "+ @nr
+"end "+ @nr+ @nr
+"if @rc_inserted>0 and @rc_deleted=0 " 
+"begin "+ @nr
+"    select @i=count(*) from LoggerTableSettings _
where LogInserts=1 and TableName=@TableName "+ @nr
+"    if @i=0 "+ @nr
+"          return "+ @nr+ @nr

+"    select  " + @fields + " into #t_inserted1 from inserted "+ @nr

+"    set @cmd='' "+ @nr
+"    declare c cursor for select name _
from syscolumns where id=object_id(@TableName) _
      and xtype not in (34,35,99)  order by colid "+ @nr
+"    open c "+ @nr
+"    fetch next from c into @FieldName "+ @nr
+"    while @@FETCH_STATUS=0 "+ @nr
+"    begin "+ @nr
+"          if @cmd='' "+ @nr
+"                set @cmd='case when [' + _
@FieldName + '] is null then ' + '''' + '''' _
                  + ' else convert(varchar(8000),_
                  ['+ @FieldName + ']) end '  "+ @nr
+"          else "+ @nr
+"                set @cmd=@cmd+  '+' +'''' + _
@separator + '''' + ' _
                   +case when [' + @FieldName + '] _
                   is null then ' + '''' + '''' + ' _
                   else convert(varchar(8000),['+ @FieldName + ']) end '  "+ @nr
+"          fetch next from c into @FieldName "+ @nr
+"    end "+ @nr
+"    close c "+ @nr
+"    deallocate c "+ @nr+ @nr

+"    set @cmd='insert into Logger_
(ModifyAction,TableName,FieldName,KeyValue,_
      NewValue,OldValue,ModifyDate,ModifyUser) select ' +_
      ''''+'INSERT' + '''' + ','+  _
      '''' + @TableName + '''' + ',' + 'null'+','+'"+@keycolsins+",_
      substring(' + @cmd + ',1,8000),null,' + _
      '''' + convert(varchar(50),getdate(),13) + _
      '''' + ',' + '''' + _
      @UserName + '''' +' from #t_inserted1' "+ @nr
+"    execute(@cmd) "+ @nr
+"    return "+ @nr
+"end "+ @nr+ @nr
+"if @rc_inserted>0 and @rc_deleted>0 "+ @nr 
+"begin "+ @nr
+"    select @i=count(*) from LoggerFieldSettings _
where  TableName=@TableName "+ @nr
+"    if @i=0 "+ @nr
+"          return "+ @nr+ @nr

+"    select  " + @fields + " into #t_inserted from inserted "+ @nr
+"    select  " + @fields + " into #t_deleted from deleted "+ @nr+ @nr

+"    if  OBJECTPROPERTY(object_id(@TableName),'TableHasIdentity')=0 "+ @nr
+"    begin "+ @nr
+"          alter table #t_inserted add _
[Logger Identity Column 1234567890 Unique Name _0987654321_] int identity(1,1) "+ @nr
+"          alter table #t_deleted add  _
[Logger Identity Column 1234567890 Unique Name _0987654321_] int identity(1,1) "+ @nr
+"    end "+ @nr+ @nr

+"    declare c cursor for select FieldName _
from LoggerFieldSettings where TableName=@TableName "+ @nr
+"    open c "+ @nr
+"    fetch next from c into @FieldName "+ @nr
+"    while @@FETCH_STATUS=0 "+ @nr
+"    begin "+ @nr
+"          select @i=count(*) from syscolumns _
where id=object_id(@TableName) and name=@FieldName "+ @nr
+"            if ((select xtype from syscolumns _
where id=object_id(@TableName) and name=@FieldName)=106 or _
(select xtype from syscolumns where id=object_id(@TableName) _
and name=@FieldName)=108) "+ @nr
+"                set @cmd='insert into Logger(ModifyAction,TableName,FieldName,KeyValue,OldValue,_
NewValue,ModifyDate,ModifyUser) select  ' +''''+'UPDATE' + _
'''' + ','+  '''' + @TableName + _
'''' + ',' + '''' + @FieldName + _
'''' +','+'"+@keycols+",_
convert(varchar(1000),#t_deleted.'+ @FieldName + '),convert(varchar(1000),_
#t_inserted.' + @FieldName + '),' + '''' + _
convert(varchar(50),getdate(),13) + '''' + ',' + _
'''' + @UserName + '''' +' _
from #t_inserted inner join #t_deleted on #t_inserted.[Logger Identity Column _
1234567890 Unique Name _0987654321_]=#t_deleted.[Logger Identity Column _
1234567890 Unique Name _0987654321_] where coalesce(#t_inserted.' + _
@FieldName  + ',0)<>coalesce(#t_deleted.'  + @FieldName + ',0)'"+ @nr
+"            else "+ @nr
+"                set @cmd='insert into Logger(ModifyAction,TableName,_
FieldName,KeyValue,OldValue,NewValue,ModifyDate,ModifyUser) select  ' +''''+'UPDATE' + '''' + ','+ _
 '''' + @TableName + '''' + ',' + '''' + @FieldName + '''' +','+'"+@keycols+",_
convert(varchar(1000),#t_deleted.'+ @FieldName + '),_
convert(varchar(1000),#t_inserted.' + @FieldName + '),' + _
'''' + convert(varchar(50),getdate(),13) + '''' + _
',' + '''' + @UserName + '''' +' _
from #t_inserted inner join #t_deleted on #t_inserted.[Logger Identity Column 1234567890 _
Unique Name _0987654321_]=#t_deleted.[Logger Identity Column 1234567890 Unique Name _0987654321_] _
where coalesce(#t_inserted.' + @FieldName  + ','''')<>_
coalesce(#t_deleted.'  + @FieldName + ','''')'"+ @nr
+"          if @i>0 "+ @nr
+"                execute(@cmd) "+ @nr
+"          fetch next from c into @FieldName "+ @nr
+"    end "+ @nr
+"    close c "+ @nr
+"    deallocate c "+ @nr
+"    return "+ @nr
+"end "+ @nr)

go

--create Demo table to demonstrate tracking changes
Create table Demo(DemoID int,DemoText varchar(100))
go

--set demonstrational tracking changes for two fields of Demo table and a table itself
insert into LoggerFieldSettings(TableName,FieldName) select 'Demo','DemoID'
insert into LoggerFieldSettings(TableName,FieldName) select 'Demo','DemoText'
insert into LoggerTableSettings(TableName,LogInserts,LogDeletes) select 'Demo',1,1

--few demo changes
insert into Demo(DemoID,DemoText) select 100,'Hello World!'
insert into Demo(DemoID,DemoText) select 200,'Hello Kitty!'
update Demo set DemoID=400 where DemoID=200
delete from Demo

--see results
select * from Logger

Points of Interest

You may adapt the code to your personal needs. There are some extra fields in Logger table that you may find useful for your own development.

Please be sure to use in your triggers or application scope_identity() function instead of @@identity variable as dynamically created trigger by this solution may interfere with the scope of the execution and therefore return unexpected results of @@identity.
BTW, using scope_identity() instead of @identity is a general good practice to avoid some hard to debug errors.

History

This code does not require any primary keys and works with most SQL Server version and with most database types. It also automatically truncates too long entries in Logger table.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)