>>Hi,
>>
>>What is a good (best) practice of maintaining value in a column of one table based on a sum values of column(s) in another table? Here is an example: Table 1 – Vendors that has a column TotalAmount; Table 2 – PurchaseOrders that has a column PoAmount. I need to maintain in the Vendors.TotalAmount sum of all values in PoAmount for each vendor. Therefore, when adding a new P.O. the value in Vendors.TotalAmount should be increased. When a P.O. is deleted it should be decreased. When P.O. amount changes, the value in Vendors.TotalAmount also should change (increased or decreased). In VFP app with VFP data I was doing it all “manually”. But I think there should be a better way of doing it with SQL Server as a back end. Looking forward to hearing suggestions. TIA.
>
>I think the best way will be to not have such a column in the parent table.
>
>However, if you need to keep it, you can create a calculated column in your table (you can even make it persistent).
>
>The calculation formula will be
>
>dbo.ufnTotalAmount(OrderID) where ufnTotalAmount will be a function in your database.
>
>I answered this question a few months ago with more code in T-SQL MSDN forum, but I believe this will be enough info for you.
>
>
>Alternative solution will be in having triggers on the Child Table(s) that will update Parent's table.
What is "persistent" column?
I am learning towards a function in database since I have never done triggers.
Thank you.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham