best way to keep a changelog

I have a couple of tables where I want to keep a changelog of all changes
made to the table, There are of course different ways to acomplish this,
currently our suggesting is a table with oldValue, newValue columns, but
this is not very flexible when it comes to datatypes and many changes to the
same row will require multiple entries.
Another way to accomplish this would be to simply create a new entry with a
newer timestamp in the table where the changelog should be on. But I'm
worried about performance if I have to filter out the newest timestamps when
i query the table, as the query performance is very important for this data.
Could anyone point me to a good article or website where I can read about
how best to create a changelog? Or could you describe it here.I would suggest to have a separate "historical" table and populate this
historical table whenever changes made to working table using trigger.
"Allan Ebdrup" <comaeb@.ofir.com> wrote in message
news:uKdquYpjFHA.1948@.TK2MSFTNGP12.phx.gbl...
> I have a couple of tables where I want to keep a changelog of all changes
> made to the table, There are of course different ways to acomplish this,
> currently our suggesting is a table with oldValue, newValue columns, but
> this is not very flexible when it comes to datatypes and many changes to
the
> same row will require multiple entries.
> Another way to accomplish this would be to simply create a new entry with
a
> newer timestamp in the table where the changelog should be on. But I'm
> worried about performance if I have to filter out the newest timestamps
when
> i query the table, as the query performance is very important for this
data.
> Could anyone point me to a good article or website where I can read about
> how best to create a changelog? Or could you describe it here.
>|||If you store archived rows in another table, you gain:
1) performance of the original table for selects won't change;
2) existing procedures (and the like) that refer to the original table need
not be changed;
3) for every row in the original table you can have several rows in the
archive table - thus creating the full history of every row.
Of course the archive table needs a new primary key, since the primary key
from the original table now becomes 'just one of the columns'. I'd also add
two more columns: ArchivedTime (datetime, default = getdate()) and ArchivedB
y
(varchar(64), default = system_user).
ML