> But, when I have several rows updated, RowMark columnes values are
> identical in all rows in the audit trail table!

>@RowMark1=d.ForeignKeyField,@RowMark2=d.PrimaryKeyField,@RowMark3=d.ID
> from deleted d

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!

> select @type = 'UPDATE',
> @RowMark1=d.ForeignKeyField,@RowMark2=d.PrimaryKeyField,@RowMark3=d.ID
>from deleted d

> 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.PrimaryKeyField,@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)