typical audit trail ?

[Zlatko Matiæ]

I tried to implement triggers for filling audit-trail table on this way.
Everything works fine as long as I don't update the primary key field value.
When I try to update PK value, an error occures.
The code is the following:

CREATE TRIGGER NameOfTheTrigger
ON dbo.TableName FOR DELETE, INSERT, UPDATE
AS BEGIN
declare
@type varchar(10) ,
@UpdateDate datetime ,
@UserName varchar(128)

if exists (select * from inserted) and exists (select * from deleted)
select @type = 'UPDATE'
else if exists (select * from inserted)
select @type = 'INSERT'
else
select @type = 'DELETE'

select @UpdateDate = getdate() ,
@UserName = system_user
/* this code is repeting for every field in the table*/
if update (TableName) or @type = 'DELETE'
insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,
UpdateDate, UserName, type)
select 'TableName', convert(varchar(20), 'FieldName'),
convert(varchar(1000),d.FieldName), convert(varchar(1000),i.FieldName),
@UpdateDate, @UserName, @type
from inserted i
full outer join deleted d
on i.PrimaryKeyFieldName = d.PrimaryKeyFieldName
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

How to slve the problem with updated (changed) primary key values?
What is the typical code for audit-trail triggers?

Thanks.

[John Bell]

Hi

If your Primary Key is likely to change then you may want to create a
manufactured primary key that does not change.

Doing a lot of processing in your triggers may increate your transaction
times. To minimize this you can simply save copies of the inserted and
deleted tables and then do your processing later.

You should check @@ROWCOUNT as the first statement in your trigger to make
sure that some records have changed.

John

"Zlatko Matiæ" <zl***********@sb.t-com.hr> wrote in message
news:d1**********@ls219.htnet.hr...

I tried to implement triggers for filling audit-trail table on this way.
Everything works fine as long as I don't update the primary key field
value. When I try to update PK value, an error occures.
The code is the following:

CREATE TRIGGER NameOfTheTrigger
ON dbo.TableName FOR DELETE, INSERT, UPDATE
AS BEGIN
declare
@type varchar(10) ,
@UpdateDate datetime ,
@UserName varchar(128)

if exists (select * from inserted) and exists (select * from deleted)
select @type = 'UPDATE'
else if exists (select * from inserted)
select @type = 'INSERT'
else
select @type = 'DELETE'

select @UpdateDate = getdate() ,
@UserName = system_user
/* this code is repeting for every field in the table*/
if update (TableName) or @type = 'DELETE'
insert dbo.AUDIT_TRAIL (TableName, FieldName, OldValue, NewValue,
UpdateDate, UserName, type)
select 'TableName', convert(varchar(20), 'FieldName'),
convert(varchar(1000),d.FieldName), convert(varchar(1000),i.FieldName),
@UpdateDate, @UserName, @type
from inserted i
full outer join deleted d
on i.PrimaryKeyFieldName = d.PrimaryKeyFieldName
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

How to slve the problem with updated (changed) primary key values?
What is the typical code for audit-trail triggers?

Thanks.

[Erland Sommarskog]

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

I tried to implement triggers for filling audit-trail table on this way.
Everything works fine as long as I don't update the primary key field
value. When I try to update PK value, an error occures.
And the error message is?

As you don't disclose the error message, I cannot really say what you
should do to fix it. However, handling changes of primary keys in a
trigger offers some special challenges. In fact, this is only possible
for single-row operations. If you update the PK value for, say, ten
rows, there is no way to relate the rows in the deleted and inserted
tables to each other. (Unless there is alternate key that is immutable.)

So when you have:
from inserted i
full outer join deleted d
on i.PrimaryKeyFieldName = d.PrimaryKeyFieldName

and ten rows has changed, this result set gives you 20 rows, effective
as there had been an insert of 10 rows and delete or 10 others.

Another note: my experience is that inserted/deleted are not very
performant, and joining them can be really expensive. For this reason
I routinely insert the tables into table variables that I call
@inserted/@deleted and work with the table variables instead.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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