home

-- Triggers 2 - Creating Audit Trails
Author Nigel Rivett


Creating Audit Trails
Author: Nigel Rivett

Objective
The objective of an audit trail is to log changes to data in tables.

This article deals with two ways of creating an audit trail:

Audit trail table for each table to be audit trailed
Create a copy of the table and write copies of the rows to it.
For this method it is usual to write the old data only as the new data is in the main table and to write an entry whether or not anything is actaully changed.
This can be implemented in triggers or in the stored procedures that update the data.
This method also does not require a primary key as it is just saving the before versions of rows upadated.

Single audit trail table
Create a table to log the table name, field name abd old and new versions of the data.
For this method it is usual to log both old and new versions of the data and only those fields that have changed.
To implement this in triggeres it is a requirement that either there is a primary key on the table or only single rows are updated.

Test table to be audit trailed.
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest]
go
create table trigtest (i_int_key int not null, j_int_key int not null, s_varchar varchar(10), t_char varchar(10), d_date datetime)
go
alter table trigtest add constraint pk primary key (i_int_key, j_int_key)
go

Test updates
insert trigtest (i_int_key, j_int_key, s_varchar, t_char, d_date)
select 1, 1, 'hello', 'goodbye', '20000101'
insert trigtest (i_int_key, j_int_key, s_varchar, t_char, d_date)
select 2, 1, 'hello', 'goodbye', '20000101'
update trigtest set s_varchar = 'helloupd' where i_int_key = 1
update trigtest set t_char = 'goodbyeupd', d_date = '20000102' where i_int_key = 1
update trigtest set t_char = null, d_date = null where i_int_key = 1
update trigtest set t_char = 'good', d_date = '20000103' where i_int_key = 1
delete trigtest where i_int_key = 1


1. Audit trail table for each table to be audited
Audit trail table
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest_au]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest_au]
go
create table trigtest_au (i_int_key int not null, j_int_key int not null, s_varchar varchar(10), t_char varchar(10), d_date datetime, UpdateDate datetime, UserName varchar(128), type varchar(10))
go

Trigger to audit trail table

To only save only the old copy of the data

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_au_trigtest]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_au_trigtest]
go
create trigger tr_au_trigtest on trigtest for update, delete
as
declare	@type varchar(1) ,
	@UpdateDate datetime ,
	@UserName varchar(128)
	if exists (select * from inserted)
		select @type = 'U'
	else
		select @type = 'D'

	select 	@UpdateDate = getdate() ,
		@UserName = system_user
	
	insert	trigtest_au (i_int_key, j_int_key, s_varchar, t_char, d_date, UpdateDate, UserName, type)
	select	i_int_key, j_int_key, s_varchar, t_char, d_date, @UpdateDate, @UserName, @type + '_old'
	from deleted
go

To save the old and new copy of the data

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_au_trigtest]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_au_trigtest]
go
create trigger tr_au_trigtest on trigtest for insert, update, delete
as
declare	@type varchar(1) ,
	@UpdateDate datetime ,
	@UserName varchar(128)
	if exists (select * from inserted) and exists (select * from deleted)
		select @type = 'U'
	else if exists (select * from inserted)
		select @type = 'I'
	else
		select @type = 'D'

	select 	@UpdateDate = getdate() ,
		@UserName = system_user
	
	insert	trigtest_au (i_int_key, j_int_key, s_varchar, t_char, d_date, UpdateDate, UserName, type)
	select	i_int_key, j_int_key, s_varchar, t_char, d_date, @UpdateDate, @UserName, @type + '_old'
	from deleted
	insert	trigtest_au (i_int_key, j_int_key, s_varchar, t_char, d_date, UpdateDate, UserName, type)
	select	i_int_key, j_int_key, s_varchar, t_char, d_date, @UpdateDate, @UserName, @type + '_new'
	from inserted
go

2. Single Audit Trail table.
Audit trail table
if exists (select * from sysobjects where id = object_id(N'[dbo].[trigtest_au]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[trigtest_au]
go
create table trigtest_au (TableName varchar(128), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000),  UpdateDate datetime, UserName varchar(128), type varchar(1))
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_au_trigtest]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_au_trigtest]
go
create trigger tr_au_trigtest on trigtest for insert, update, delete
as
declare	@type varchar(1) ,
	@UpdateDate datetime ,
	@UserName varchar(128)
	if exists (select * from inserted) and exists (select * from deleted)
		select @type = 'U'
	else if exists (select * from inserted)
		select @type = 'I'
	else
		select @type = 'D'

	select 	@UpdateDate = getdate() ,
		@UserName = system_user

	if update (i_int_key) or @type = 'D'
		insert trigtest_au (TableName, FieldName, OldValue, NewValue, UpdateDate, UserName, type)
		select 	'type', 'i_int_key', convert(varchar(1000),d.i_int_key), convert(varchar(1000),i.i_int_key), @UpdateDate, @UserName, @type
		from	inserted i 
				full outer join deleted d
					on i.i_int_key = d.i_int_key
					and i.j_int_key = d.j_int_key
		where (i.i_int_key <> d.i_int_key or (i.i_int_key is null and d.i_int_key is not null) or (i.i_int_key is not null and d.i_int_key is null))
	if update (j_int_key) or @type = 'D'
		insert trigtest_au (TableName, FieldName, OldValue, NewValue, UpdateDate, UserName, type)
		select 	'type', 'j_int_key', convert(varchar(1000),d.j_int_key), convert(varchar(1000),i.j_int_key), @UpdateDate, @UserName, @type
		from	inserted i 
				full outer join deleted d
					on i.i_int_key = d.i_int_key
					and i.j_int_key = d.j_int_key
		where (i.j_int_key <> d.j_int_key or (i.j_int_key is null and d.j_int_key is not null) or (i.j_int_key is not null and d.j_int_key is null))
	if update (s_varchar) or @type = 'D'
		insert trigtest_au (TableName, FieldName, OldValue, NewValue, UpdateDate, UserName, type)
		select 	'type', 's_varchar', convert(varchar(1000),d.s_varchar), convert(varchar(1000),i.s_varchar), @UpdateDate, @UserName, @type
		from	inserted i 
				full outer join deleted d
					on i.i_int_key = d.i_int_key
					and i.j_int_key = d.j_int_key
		where (i.s_varchar <> d.s_varchar or (i.s_varchar is null and d.s_varchar is not null) or (i.s_varchar is not null and d.s_varchar is null))
	if update (t_char) or @type = 'D'
		insert trigtest_au (TableName, FieldName, OldValue, NewValue, UpdateDate, UserName, type)
		select 	'type', 't_char', convert(varchar(1000),d.t_char), convert(varchar(1000),i.t_char), @UpdateDate, @UserName, @type
		from	inserted i 
				full outer join deleted d
					on i.i_int_key = d.i_int_key
					and i.j_int_key = d.j_int_key
		where (i.t_char <> d.t_char or (i.t_char is null and d.t_char is not null) or (i.t_char is not null and d.t_char is null))
	if update (d_date) or @type = 'D'
		insert trigtest_au (TableName, FieldName, OldValue, NewValue, UpdateDate, UserName, type)
		select 	'type', 'd_date', convert(varchar(1000),d.d_date), convert(varchar(1000),i.d_date), @UpdateDate, @UserName, @type
		from	inserted i 
				full outer join deleted d
					on i.i_int_key = d.i_int_key
					and i.j_int_key = d.j_int_key
		where (i.d_date <> d.d_date or (i.d_date is null and d.d_date is not null) or (i.d_date is not null and d.d_date is null))
go


home