Sql Server. Triggers 2 – Creating Audit Trails – Author Nigel Rivett

— 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:

1. 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 updated.

2. 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.

Note that text and image columns are not available in the insertd/deleted tables to after triggers so these cannot be saved by this method.

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 create the audit trail for the 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

Trigger to save old and new values of fields that have changed

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, PK, FieldName, OldValue, NewValue, UpdateDate, UserName, type)
select ‘trigtest’, convert(varchar(20),coalesce(d.i_int_key,i.i_int_key)) + ‘|’ + convert(varchar(20),coalesce(d.j_int_key,i.j_int_key)), ‘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, PK, FieldName, OldValue, NewValue, UpdateDate, UserName, type)
select ‘trigtest’, convert(varchar(20),coalesce(d.i_int_key,i.i_int_key)) + ‘|’ + convert(varchar(20),coalesce(d.j_int_key,i.j_int_key)), ‘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, PK, FieldName, OldValue, NewValue, UpdateDate, UserName, type)
select ‘trigtest’, convert(varchar(20),coalesce(d.i_int_key,i.i_int_key)) + ‘|’ + convert(varchar(20),coalesce(d.j_int_key,i.j_int_key)), ‘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, PK, FieldName, OldValue, NewValue, UpdateDate, UserName, type)
select ‘trigtest’, convert(varchar(20),coalesce(d.i_int_key,i.i_int_key)) + ‘|’ + convert(varchar(20),coalesce(d.j_int_key,i.j_int_key)), ‘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, PK, FieldName, OldValue, NewValue, UpdateDate, UserName, type)
select ‘trigtest’, convert(varchar(20),coalesce(d.i_int_key,i.i_int_key)) + ‘|’ + convert(varchar(20),coalesce(d.j_int_key,i.j_int_key)), ‘d_date’, convert(char(9),d.d_date,112) + convert(char(8),d.d_date,8), convert(char(9),i.d_date,112) + convert(char(8),i.d_date,8), @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

This gives the following result

select TableName = convert(char(12),TableName), PK = convert(char(6),PK), FieldName = convert(char(16),FieldName),
OldValue = convert(char(20),OldValue), NewValue = convert(char(20), NewValue),
UpdateDate = convert(char(9),UpdateDate,112)+convert(char(10),UpdateDate,8), UserName = convert(char(10),UserName), type
from trigtest_au

TableName PK FieldName OldValue NewValue UpdateDate UserName type
———— —— —————- ——————– ——————– ——————- ———- —-
trigtest 1|1 i_int_key NULL 1 20030330 04:00:14 sa I
trigtest 1|1 j_int_key NULL 1 20030330 04:00:14 sa I
trigtest 1|1 s_varchar NULL hello 20030330 04:00:14 sa I
trigtest 1|1 t_char NULL goodbye 20030330 04:00:14 sa I
trigtest 1|1 d_date NULL 20000101 00:00:00 20030330 04:00:14 sa I
trigtest 2|1 i_int_key NULL 2 20030330 04:00:14 sa I
trigtest 2|1 j_int_key NULL 1 20030330 04:00:14 sa I
trigtest 2|1 s_varchar NULL hello 20030330 04:00:14 sa I
trigtest 2|1 t_char NULL goodbye 20030330 04:00:14 sa I
trigtest 2|1 d_date NULL 20000101 00:00:00 20030330 04:00:14 sa I
trigtest 1|1 s_varchar hello helloupd 20030330 04:00:14 sa U
trigtest 1|1 t_char goodbye goodbyeupd 20030330 04:00:14 sa U
trigtest 1|1 d_date 20000101 00:00:00 20000102 00:00:00 20030330 04:00:14 sa U
trigtest 1|1 t_char goodbyeupd NULL 20030330 04:00:14 sa U
trigtest 1|1 d_date 20000102 00:00:00 NULL 20030330 04:00:14 sa U
trigtest 1|1 t_char NULL good 20030330 04:00:14 sa U
trigtest 1|1 d_date NULL 20000103 00:00:00 20030330 04:00:14 sa U
trigtest 1|1 i_int_key 1 NULL 20030330 04:00:14 sa D
trigtest 1|1 j_int_key 1 NULL 20030330 04:00:14 sa D
trigtest 1|1 s_varchar helloupd NULL 20030330 04:00:14 sa D
trigtest 1|1 t_char good NULL 20030330 04:00:14 sa D
trigtest 1|1 d_date 20000103 00:00:00 NULL 20030330 04:00:14 sa D