audit trail triggers

[Zlatko Matiæ]

Hello.

I tried to implement audit trail, by making an audit trail table with the
following fileds:
TableName,FieldName,OldValue,NewValue,UpdateDate,t ype,UserName.
Triggers on each table were set to do the job and everything was fine except
that in the audit trail you couldn't know which row exacltly was
updated/inserted/deleted...Therefore I introduced 3 additional columnes
(RowMark1, RowMark2, RowMark3) which should identify the
inserted/updated/deleted row.
For example, RowMark1 could be foreign key, RowMark2 could be primary key,
and RowMark3 could be autonumber ID.
But, when I have several rows updated, RowMark columnes values are identical
in all rows in the audit trail table! What is wrong with my code, and how to
solve it ?

Thank you in advance!
CREATE TRIGGER Trigger_audit_TableName
ON dbo.TableName
FOR DELETE, INSERT, UPDATE
AS BEGIN
declare @type nvarchar(20) ,
@UpdateDate datetime ,
@UserName nvarchar(100),
@RowMark1 nvarchar (100),
@RowMark2 nvarchar (100),
@RowMark3 nvarchar (100)

if exists (select * from inserted) and exists (select * from
deleted)
select @type = 'UPDATE',
@RowMark1=d.ForeignKeyField,@RowMark2=d.PrimaryKey Field,@RowMark3=d.ID
from deleted d
else if exists (select * from inserted)
select @type = 'INSERT',
@RowMark1=i.ForeignKeyField,@RowMark2=i.PrimaryKey Field,@RowMark3=i.ID
from inserted i
else
select @type = 'DELETE',
@RowMark1=d.ForeignKeyField,@RowMark2=d.PrimaryKey Field,@RowMark3=d.ID
from deleted d

select @UpdateDate = getdate() ,
@UserName = USER
/*The following code is repeated for every field in a table*/
if update (FieldName) or @type = 'DELETE'
insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,
UpdateDate, UserName, type,RowMark1,RowMark2,RowMark3)
select 'Descriptive Table Name', convert(nvarchar(100), 'Descriptive
Field Name'),
convert(nvarchar(1000),d.FieldName),
convert(nvarchar(1000),i.FieldName),
@UpdateDate, @UserName, @type, @RowMark1, @RowMark2,
@RowMark3
from inserted i
full outer join deleted d
on i.ID = d.ID
where (i.FieldName <> d.FieldName
or (i.FieldName is null and d.FieldName is not null)
or (i.FieldName is not null and d.FieldName is null))

END

[Hugo Kornelis]

On Thu, 31 Mar 2005 20:10:46 +0200, Zlatko Matiæ wrote:

Hello.

I tried to implement audit trail, by making an audit trail table with the
following fileds:
TableName,FieldName,OldValue,NewValue,UpdateDate, type,UserName.
Triggers on each table were set to do the job and everything was fine except
that in the audit trail you couldn't know which row exacltly was
updated/inserted/deleted...Therefore I introduced 3 additional columnes
(RowMark1, RowMark2, RowMark3) which should identify the
inserted/updated/deleted row.
For example, RowMark1 could be foreign key, RowMark2 could be primary key,
and RowMark3 could be autonumber ID.
But, when I have several rows updated, RowMark columnes values are identical
in all rows in the audit trail table! What is wrong with my code, and how to
solve it ?

Thank you in advance!
Hi Zlatko,

A trigger is fired once per statement execution, not once per row
affected. The changes you made, like for example this one:
select @type = 'UPDATE',
@RowMark1=d.ForeignKeyField,@RowMark2=d.PrimaryKey Field,@RowMark3=d.ID
from deleted d

will set the variables to the values for one of the rows that were
affected by the update. This variable is then used in all inserts to the
audit table!

You should forget the @RowMark1, -2, and -3 variables. Instead, change
the code to insert audit data to something like this:

insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,
UpdateDate, UserName, type,RowMark1,RowMark2,RowMark3)
select 'Descriptive Table Name', convert(nvarchar(100), 'Descriptive
Field Name'),
convert(nvarchar(1000),d.FieldName),
convert(nvarchar(1000),i.FieldName),
@UpdateDate, @UserName, @type,
COALESCE (d.ForeignKeyField, i.ForeignKeyField),
COALESCE (d.PrimaryKeyField, i.PrimaryKeyField),
COALESCE (d.ID, i.ID)
from inserted i
full outer join deleted d
on i.ID = d.ID
where (i.FieldName <> d.FieldName
or (i.FieldName is null and d.FieldName is not null)
or (i.FieldName is not null and d.FieldName is null))

I'd like to add that I'm not very fond of your audit table design. I
personally prefer to use several audit tables: one for each table that
needs auditing, with the same columns, plus extra columns such as
DatetimeOfChange (added as extra column to the primary key) and userid.
This table will receive a complete copy of a row's data whenever it is
changed. But hey - if this design works for you, then by all means use
it.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Zlatko Matiæ (zl***********@sb.t-com.hr) writes:

But, when I have several rows updated, RowMark columnes values are
identical in all rows in the audit trail table!
Of course:
@RowMark1=d.ForeignKeyField,@RowMark2=d.PrimaryKe yField,@RowMark3=d.ID
from deleted d

If deleted contains 15 rows, how would @RowMark1 get more than one value?

Yes, triggers fires once per statement.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Zlatko Matic]

Thanks.

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> je napisao u poruci interesnoj
grupi:9h********************************@4ax.com.. .

On Thu, 31 Mar 2005 20:10:46 +0200, Zlatko Matiæ wrote:

Hello.

I tried to implement audit trail, by making an audit trail table with the
following fileds:
TableName,FieldName,OldValue,NewValue,UpdateDate ,type,UserName.
Triggers on each table were set to do the job and everything was fine
except
that in the audit trail you couldn't know which row exacltly was
updated/inserted/deleted...Therefore I introduced 3 additional columnes
(RowMark1, RowMark2, RowMark3) which should identify the
inserted/updated/deleted row.
For example, RowMark1 could be foreign key, RowMark2 could be primary key,
and RowMark3 could be autonumber ID.
But, when I have several rows updated, RowMark columnes values are
identical
in all rows in the audit trail table! What is wrong with my code, and how
to
solve it ?

Thank you in advance!

Hi Zlatko,

A trigger is fired once per statement execution, not once per row
affected. The changes you made, like for example this one:

select @type = 'UPDATE',

@RowMark1=d.ForeignKeyField,@RowMark2=d.PrimaryKey Field,@RowMark3=d.ID
from deleted d

will set the variables to the values for one of the rows that were
affected by the update. This variable is then used in all inserts to the
audit table!

You should forget the @RowMark1, -2, and -3 variables. Instead, change
the code to insert audit data to something like this:

insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,
UpdateDate, UserName, type,RowMark1,RowMark2,RowMark3)
select 'Descriptive Table Name', convert(nvarchar(100), 'Descriptive
Field Name'),
convert(nvarchar(1000),d.FieldName),
convert(nvarchar(1000),i.FieldName),
@UpdateDate, @UserName, @type,
COALESCE (d.ForeignKeyField, i.ForeignKeyField),
COALESCE (d.PrimaryKeyField, i.PrimaryKeyField),
COALESCE (d.ID, i.ID)
from inserted i
full outer join deleted d
on i.ID = d.ID
where (i.FieldName <> d.FieldName
or (i.FieldName is null and d.FieldName is not null)
or (i.FieldName is not null and d.FieldName is null))

I'd like to add that I'm not very fond of your audit table design. I
personally prefer to use several audit tables: one for each table that
needs auditing, with the same columns, plus extra columns such as
DatetimeOfChange (added as extra column to the primary key) and userid.
This table will receive a complete copy of a row's data whenever it is
changed. But hey - if this design works for you, then by all means use
it.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)