Dynamic Update Statement for Audit Trail

[rvancleef]

I need to be able to audit changes made on a form. Changes to each field must be reflected in the audit trail. Rather than writing a seperate SP for each of the 30+ fields, I would like to know if the Update statement could be written dynamically by passing parameters to a stored procedure.

Through ASP, I can use the request.form to identify a field name and the new value, as well as the record ID and UserID.

I would like to write a SP that will take these four pieces of information and use them as parameters as follows:

1. Begin a transaction
2. Write an entry to the audit table with the UserID, FieldName, CurrentValue(from DB), New Value (from form)
3. Write an Update to the record (as identified by the record id) on the selected field with the new value.
4. Commit the transaction

 

[SQLSister]

I would write a trigger to write to the audit table, that way if anyone changes the data directly you will also record it in the audit table.

 

[rvancleef]

Do you have any sense if it is possible to pass field name and value parameters to a SP to write the update statement on the fly?

 

[SQLSister]

Yes you can do that but it requires dynamic SQL which is not very efficient. Since you are grabbing this from a form, you know what fields it has and can pass the parameters all at once to one update statement. This will be more efficient and does not require the dba to give direct rights to the tables.