Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dogli
Starting Member
32 Posts |
 Posted - 2002-12-09 : 13:29:46
|
| Hi guys,I have a table as below:table_weightweightID int,wight int,old_weight int,updatedDate datetimeMy user needs to update the weight in this table with unlimited times. What I need to do is keep record of all the changes made to this table, and print a report out.e.g: the first time, the user update weight from 10 to 20, I need to set weight = 20, old_weight = 10, updatedDate =XXXX.And the user can upates as many as they like. My question is how can I keep record for these changes as I don't know how many times they would update? so I don't know how many columns need. Can I insert column in the table dynamically? or is there any other goood methods to do this?BIG thanks for all of your help. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-09 : 13:39:38
|
| Whatever approach you use, DON'T dynamically create or alter columns to store old values, that will be a disaster very quickly.Check this article:http://www.sqlteam.com/redir.asp?ItemID=9182And search SQL Team (regular search AND forum search) for "audit", you'll find a good amount of information on creating audit tables. These have a fixed structure that store old (and new, if you choose) values of rows that were updated, and include dates and logins of those changes. |
 |
|
|
dogli
Starting Member
32 Posts |
Posted - 2002-12-09 : 14:37:02
|
| Thank you so much, robvolk! You give me a good hint! |
|
|
|
|
|
|
|
|
Dynamically create table column? Possible?