Joel,

No, the SQL transaction log is not designed to do the function you are wanting. You will have to put a trigger in place to do this.

How many tables are you wanting to log like this? You might also consider using a log table per table rather than using a common log table. It'd be a lot easier to replicate the row into the same structure table.

You also need to consider the performance impact this will have on your system.

>I need suggestions for permanent Data file change log in SQL 2000.
>
>I would like to use a trigger on every table in a system to write a record to a data change log table for any data change. It is a Dot Net 1.1 web system using SQL 2000.
>
>When any record is changed, I want to know the table name, the field, the before and after value. I will also store date/time and user ID from a session variable.
>
>Obviously any given change could involve one field or dozens of fields in numerous tables.
>
>I do not think the SQL transaction log will do that, but I am not sure.