George,

Interesting questions ;-) I have actually the exact same requirement for an application that I am working on. I really like Bob's idea of using a UNION and to check the record count.

Unfortunately UPDATE_COLUMN() and UPDATE() functions return true even if the data has not been changed. It really depends how the update command has been specified. In VFP terms, any field in a remote view for which GETFLDSTATE() indicates that the field has been changed will cause UPDATE_COLUMN() to return true irrespective of the actual values.

I've been using a meta-data driven script-generator to create the audit trail triggers. This way, I have to code the basic logic only in one place and can recreate the triggers. It's easy to retreive the key field(s) with sp_pkeys and to exclude certain fields (e.g. timestamp fields).

I'll change my script generator to use BOb's logic, join the two tables based on the key field(s) and include only the fields to be audited in the SELECT list.

If someone has an easier solution, I'm all ears ;-)

>>>So the first question is simply, is there an easier way, other than comparing all the other fields in the inserted table against the same fields in the same record in the deleted table? Further, is there any way to do it generically? IOW, without having to hard code the actual fields names into the trigger. If this simply involved one or two fields, it would be relatively easy.
>>>
>>
>>Just a thought, and this depends on if the 'last update date' is comming in different or the same. One thought is to select distinct a union of INSERT and DELETED tables... if the rowcount is 1 then there are no changes, if the rowcount is 2 then there are changes. This assumes the lastupdate field is not changed in the update, and the trigger is responsible for putting that value in there.
>
>This might work, but with batch updates, I'd need to track the PK, which might be possible if identity columns were being used, but I don't think that it does. I'd have to check, but that's one suggestion to consider. Thanks, BOb
>
>>If the last date does come in changed, you may be able to create a temp table with the last date changed back to the original so it won't be considered, but this would have a performance hit, and may require less generic code.
>>
>>To be more generic you can probably use the COLUMNS_UPDATED() function in the trigger. This returns a string of bits which indicate if the fields where changed. You could look at all the bits except the one for the datestamp to determine if any changed. Although, I am not sure if this will show changed even if the update statement sends the same value, it may just flag each column included in the UPDATE as 'changed'.
>
>Now that's a thought, and at least something for me to look into.
>
>>>The second question is, assuming that there isn’t another way, can anyone give me any idea of the degree of severity this will impact performance? The tables are relatively small (1000-2000 records), with about an average of 20 fields to be compared.
>>>
>>
>>It should be minimal... espesially if you write pretty straingforward code... Also, are you expeciting batch updates or is it sending one update at at time?
>
>Both really.
>
>>Where you planning on ROLLING BACK the update if it isn't really an update, or just setting the datechanged back to the orig value?
>
>I hadn't really decided, since I've seen examples of both techniques. I'm really not sure what would be the most effective.
>
>Thanks again, BOb