sql server 2008 - Calcualting fileds through trigger in MSSQL? -

i have 2 tables product , shop have trigger works when product added shop, updates product quantity shop when inserted , total price fields of product quantity , unit price. quantity updated correctly.

the problem is: total price not updated correctly. problem?

alter trigger [dbo].[trg_updtproqty]  on [dbo].[shop] insert  begin     -- update product quantity , total price shop table     update pd     set pd.qty = pd.qty-i.qty, pd.tprice=pd.uprice*pd.qty     dbo.product pd     inner join inserted on pd.pid = i.pid         end 

updates not happen in order write them in, i.e. in original update statement have:

set pd.qty = pd.qty-i.qty,      pd.tprice=pd.uprice*pd.qty 

when setting tprice value of pd.qty still original value, should

set pd.qty = pd.qty-i.qty,      pd.tprice=pd.uprice*(pd.qty-i.qty) 

if tprice such simple calculation , may want not bother storing @ all, , use computed column:

alter table dbo.product drop column tprice; go alter table dbo.product add tprice uprice * qty; 

then wouldn't have worry keeping date, automatically refresh whenever quantity or price changed.

---

addendum

in order capture updates shop table, need make trigger insert , update, need reference memory resident table deleted in trigger find out actual change is, given deleted.qty - inserted.qty, trigger end like:

alter trigger [dbo].[trg_updtproqty] on [dbo].[shop] insert, update  begin     -- update product quantity , total price shop table     update pd     set     pd.qty = pd.qty + (isnull(d.qty, 0) - i.qty),              pd.tprice = pd.uprice * (pd.qty + (isnull(d.qty, 0) - i.qty))        dbo.product pd             inner join inserted                  on pd.pid = i.pid                     left join deleted d                 on d.pid = pd.pid; end