0

I have two tables one is for Egg sales to customers and another has a list of prices.

Everything is working perfectly except when I update the price list, when I update the price in pricelist I don't want the calculated price in old records to change. But in my case if I update the pricelist, old entries also change which is not correct for payments already done

Example 

- **price_type**       **price_amount**
    big_eggs_tray             500    
   small_eggs_tray           250

and say I do a sale of 2 eggs to my customer. Which makes my sales table look like this

id    customer_name  price_type       eggs/trays   total  amount_paid  remaining
11    Bob            big_eggs_tray       2         1000       250          750

Now when I update the price list table I don't want my old records to change. how do I achieve this.

Please help my application is all done but I have run into this bug on testing phase. I'm using SQL Server and ASP.NET

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bob269
  • 115
  • 1
  • 13
  • 1
    Your old records aren't updating themselves. Either your asp code behind is sending calls to the database or the database has a calculated field or a trigger on update or something like that. – Steve Wellens Nov 16 '14 at 13:35
  • Please post the list of columns in your Price List table. If the Price List table has any unique indexes, that would be useful information as well. – Mike Jones Nov 16 '14 at 16:31
  • Well i managed to solve it. But here is a list of columns in price list ** price_id, price_type,amount, description .... But what i did to solve is i added start and end date column. And so i wont be updating prices but rather old ones will expire and i will be able to maintain their values in both price list table and sales table. Is there an alternative solution guys. I would like to know. – bob269 Nov 17 '14 at 13:50
  • @steve wellens. There was no trigger tht i programmed but it was an automatic one because as u said the total column is a calculated column so whatever update i made to price list it was being reflected on the sales table – bob269 Nov 17 '14 at 14:04

1 Answers1

0

would you make sure that you are not updating the the fields which the calculating field dependent on? also, please have a look at this answer: https://stackoverflow.com/a/5328211/3316279

Community
  • 1
  • 1
MarwaAhmad
  • 808
  • 9
  • 21