Audit Trail Triggers

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