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 |
|
Rauken
Posting Yak Master
108 Posts |
 Posted - 2007-03-02 : 05:14:50
|
| I have a small webapplication, sql server 2000. The users can only update the data in the system. However my client needs a report that display changes. The changes are that needs to be monitored are only change of order status, change of delivery date and when a user splits an order. What is the best practice to keep track of changes? A mirror table for each table with changes? /Magnus |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-03-02 : 08:05:43
|
| Yes, it's best to create an audit table for each table you want to track changes on. This article should help:[url]http://www.4guysfromrolla.com/webtech/091901-1.shtml[/url]Mark |
 |
|
|
Kristen
Test
22859 Posts |
|
|
KenS
Starting Member
6 Posts |
Posted - 2007-08-23 : 11:57:33
|
| Let's take this one or two steps further... I have a similar need plus, I want to keep a status on my rows. Valid values for the status are 'Active', 'Inactive' and 'Deleted'. I also have a User table with UserId (int) as the PK. [User is a reserved word and is not the real name of my table. I'm using it here for simplicity.] So, I want to consider using the UserId in the history instead of varchar Modifier as the example does. Since I don't believe that my list of statuses will ever change and I have lots of tables to keep a status on, I have opted to make Account.StatusCode char(1) with valid values 'A', 'I' and 'D'. I chose not to make Status a lookup table. I have opted for a check constraint on the column instead.I also added LastActionUserId to the Account table. I did this primarily to help the trigger find the UserId rather than rely on a function or other processing. My stored procedures will know the UserId.The tables now look like this:create table "Account" ( "AccountId" int identity not null,"AccountName" varchar(25) not null,"AccountBalance" money not null,"StatusCode" char(1) not null constraint "Account_StatusCode_CK1" check ("StatusCode" in ('A','I','D')) ,"LastActionUserId" int not null) create table "AccountHistory" ( "AccountHistoryId" int identity not null,"AccountId" int not null,"AccountName" varchar(25) not null,"AccountBalance" money not null,"StatusCode" char(1) not null,"ActionDateTime" datetime not null,"ActionCode" char(1) not null constraint "AccountHistory_ActionCode_CK1" check ("ActionCode" in ('U','I')) ,"ActionUserId" int not null) I did not create a foreign key constraint on AccountHistory.ActionUserId to the User table. I will have lots of history tables and the RI is done on the main table (Account) anyway. I have opted to create a non-unique index instead so that joins to User will perform well.I am still concerned that having hundreds of tables with a foreign key constraint to User will eventually cause performance problems. I can eliminate the LastActionUserId from the main tables and that will cut the count of FKs to User in half. But, I am very concerned about trigger performance. My gut tells me my approach is better but I have not done this on a large scale. Any thoughts on the trade-off between having lots of FKs to a single table vs. trigger performance? |
|
|
|
|
|
|
|
|
Row versioning, audit, best practice