Recording Changes to Records in SQL Server 2000 (SP3a)

Hi all,
I am interested in your feed back to this question and I know their may be
lots of replies and I am hoping with your replies it might give me an idea as
to go.
I have a SQL Server 2000 Database with loads of table for storing
information about people, addresses, products we sell all that kind of stuff.
What I would like to do is have some way of recoring all the changes made to
any entry so that we can view a history of lets say address changes. I am
looking for SQL Server 2000 to do all this recording automaticly for me. I
don't want to have to create more tables to store the old data. I just want
away of recording each change, so if only 1 column is changed then only the
columns previous value is stored, with details of when it was changed and by
who.
A modal or your ideas will be of great help.
Dr. Paul Caesar
CoullByte (UK) Limited
YOu can buy a product (www.lumigent.com) to do this or you can do it
yourself.
Normally, one would create triggers, and add history tables. When a change
is made the trigger automatically records the history.
Since you do not wish to create any new tables, then it seems your option is
to store things in existing tables. While generally not a good idea, it can
be done, but you would have to add some sort of flag to differentiate
between the LIVE rows and the history rows.. You would still write code and
put it into a trigger...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dr. Paul Caesar - CoullByte (UK) Limited"
<DrPaulCaesarCoullByteUKLimited@.discussions.micros oft.com> wrote in message
news:52652137-FFAD-4A87-B9B0-159418A5FE55@.microsoft.com...
> Hi all,
> I am interested in your feed back to this question and I know their may be
> lots of replies and I am hoping with your replies it might give me an idea
as
> to go.
> I have a SQL Server 2000 Database with loads of table for storing
> information about people, addresses, products we sell all that kind of
stuff.
> What I would like to do is have some way of recoring all the changes made
to
> any entry so that we can view a history of lets say address changes. I am
> looking for SQL Server 2000 to do all this recording automaticly for me. I
> don't want to have to create more tables to store the old data. I just
want
> away of recording each change, so if only 1 column is changed then only
the
> columns previous value is stored, with details of when it was changed and
by
> who.
> A modal or your ideas will be of great help.
> Dr. Paul Caesar
> CoullByte (UK) Limited
|||"Dr. Paul Caesar - CoullByte (UK) Limited"
<DrPaulCaesarCoullByteUKLimited@.discussions.micros oft.com> wrote in message
news:52652137-FFAD-4A87-B9B0-159418A5FE55@.microsoft.com...
> Hi all,
> I am interested in your feed back to this question and I know their may be
> lots of replies and I am hoping with your replies it might give me an idea
> as
> to go.
> I have a SQL Server 2000 Database with loads of table for storing
> information about people, addresses, products we sell all that kind of
> stuff.
> What I would like to do is have some way of recoring all the changes made
> to
> any entry so that we can view a history of lets say address changes. I am
> looking for SQL Server 2000 to do all this recording automaticly for me. I
> don't want to have to create more tables to store the old data. I just
> want
> away of recording each change, so if only 1 column is changed then only
> the
> columns previous value is stored, with details of when it was changed and
> by
> who.
> A modal or your ideas will be of great help.
> Dr. Paul Caesar
> CoullByte (UK) Limited
I have seen (and implemented) a solution to this with a single history
table. I then created triggers on the tables I wished to track. In the
history table, I have stored a simple XML document (as an nvarchar) which
looks similar to the following:
<row DateModified=' ' TableName=' ' ColumnName=' ' OldValue=' '
NewValue=' ' ModifiedBy=' ' />
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Hi all, again, thanks for input so far, some more information which might help.
Lets say I have an Address table and a Orders table for a customer. If that
customer changes their address I don't want all the previous orders to lose
the previous address information. So I link the order address with the
address table. This allows me to follow orders at addresses to help aid fraud
prevation and also saves us recording duplicate information in the database.
Also if say a cutomer chages their surname I want the previous orders to keep
the old details but the old details will be marked in some way so we know it
was changed. Another example would be to have a product price table so we
could track price changes in a product or category of products.
Hope this extra information helps.
Dr. Paul Caesar
CoullByte (UK) Limited