資訊與工作

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

Sql Server. Generic audit trail trigger – Author Nigel Rivett

Generic audit trail trigger
Author Nigel Rivett

/*
This trigger audit trails all changes made to a table.
It will place in the table Audit all inserted, deleted, changed columns in the table on which it is placed.
It will put out an error message if there is no primary key on the table
You will need to change @TableName to match the table to be audit trailed
*/

–Set up the tables
if exists (select * from sysobjects where id = object_id(N’[dbo].[Audit]‘) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[Audit]
go
create table Audit (Type char(1), TableName varchar(128), PK varchar(1000), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000), UpdateDate datetime, UserName varchar(128))
go
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 not null, j int not null, s varchar(10), t varchar(10))
go
alter table trigtest add constraint pk primary key (i, j)
go

create trigger tr_trigtest on trigtest for insert, update, delete
as

declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKSelect varchar(1000)

select @TableName = ‘trigtest’

– date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + ‘ ‘ + convert(varchar(12), getdate(), 114)

– Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ‘U’
else
select @Type = ‘I’
else
select @Type = ‘D’

– get list of columns
select * into #ins from inserted
select * into #del from deleted

– Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + ‘ and’, ‘ on’) + ‘ i.’ + c.COLUMN_NAME + ‘ = d.’ + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ‘PRIMARY KEY’
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

– Get primary key select for insert
select @PKSelect = coalesce(@PKSelect+’+',』) + 』’<’ + COLUMN_NAME + ‘=』+convert(varchar(100),coalesce(i.’ + COLUMN_NAME +’,d.’ + COLUMN_NAME + ‘))+』>』’
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ‘PRIMARY KEY’
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @PKCols is null
begin
raiserror(‘no PK on table %s’, 16, -1, @TableName)
return
end

select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field – 1 )% 8 + 1
select @bit = power(2,@bit – 1)
select @char = ((@field – 1) / + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (‘I’,'D’)
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select @sql = ‘insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)’
select @sql = @sql + ‘ select 』’ + @Type + 』』
select @sql = @sql + ‘,』’ + @TableName + 』』
select @sql = @sql + ‘,’ + @PKSelect
select @sql = @sql + ‘,』’ + @fieldname + 』』
select @sql = @sql + ‘,convert(varchar(1000),d.’ + @fieldname + ‘)’
select @sql = @sql + ‘,convert(varchar(1000),i.’ + @fieldname + ‘)’
select @sql = @sql + ‘,』’ + @UpdateDate + 』』
select @sql = @sql + ‘,』’ + @UserName + 』』
select @sql = @sql + ‘ from #ins i full outer join #del d’
select @sql = @sql + @PKCols
select @sql = @sql + ‘ where i.’ + @fieldname + ‘ <> d.’ + @fieldname
select @sql = @sql + ‘ or (i.’ + @fieldname + ‘ is null and d.’ + @fieldname + ‘ is not null)’
select @sql = @sql + ‘ or (i.’ + @fieldname + ‘ is not null and d.’ + @fieldname + ‘ is null)’
exec (@sql)
end
end
go

insert trigtest select 1,1,’hi’, ‘bye’
insert trigtest select 2,2,’hi’, ‘bye’
insert trigtest select 3,3,’hi’, ‘bye’
select * from Audit
select * from trigtest
update trigtest set s = ‘hibye’ where i <> 1
update trigtest set s = ‘bye’ where i = 1
update trigtest set s = ‘bye’ where i = 1
update trigtest set t = ‘hi’ where i = 1
select * from Audit
select * from trigtest
delete trigtest
select * from Audit
select * from trigtest

go
drop table Audit
go
drop table trigtest
go