Thanks for responding Sergey. It's a pretty small system, so I'm not that concerned about performance. I'll have Agent go out and purge old records once a month, so file growth also isn't a problem either.

Are you saying that I should remove the trigger code and initiate the code from my application (via SPT)? If so, I'd rather leave the code in the trigger, so the audit record gets created regardless of which client application updates it.

Also, would I be able to access the inserted and deleted tables from within the SP if I took my original approach?

Thanks again for all the help.

Robert



>For performance reason I would siggest that you approch this task from different angle. Create program/application that will generate a trigger script for each table in DB.
>
>>I am writing an audit log which will store old and new values when a given record changes. Instead of hardcoding every field name in my update triggers (a coding and maintenance nightmare), I am hoping to be able to write a stored procedure/UDF that will do the job.
>>
>>Here's the approach I'm taking:
>>
>>1. Pass the object_id of the updated table to the stored procedure from within the trigger.
>>2. Within the stored procedure, I use that object_id to build a query containing all column names for the table (object).
>>3. Loop (fetch) through the result, storing the field name to a local variable.
>>4. Test each field (which is a variable) to see if it has been updated.
>>5. If it has been updated, I'll need the inserted and deleted values.
>>6. Build a string (i.e. Old Company = ABC, New Company = DEF) for all updates.
>>7. Pass that string back to the trigger so it can insert it into my audit log table.
>>
>>Does SQL Server do macro substitution? If not, any ideas how I can make this fly without hardcoding every update trigger? Will I be able to even access the inserted and deleted triggers outside the trigger?
>>
>>Thanks for the help!
>>
>>Robert