Is this a function= (very urgent)

# Thread: Is this a function= (very urgent)

1. Junior Member
Join Date
Dec 1969
Posts
2

## Is this a function= (very urgent)

Is a function what I need?<BR><BR>this is the big picture<BR><BR>Table1: ORDERS / Fields: ORDERS.ORDERID, ORDERS.TOTALCOST<BR>Table2: ORDERDETAILS / Fields: ORDERDETAILS.ORDERID, ORDERDETAILS.ITEMCOST<BR><BR>So, there it is. There will be one or multiple rows in ORDERDETAILS for each<BR>row in ORDERS. I need that the field ORDERS.TOTALCOST contains the value of<BR>the sumatory of the ORDERDETAILS.ITEMCOST corresponding to it´s ORDERID. I need the ORDERS.TOTALCOST row to be updated when changes are made in the ORDERDETAILS table.<BR><BR>kind of "SELECT SUM(ORDERDETAILS.ITEMCOST) WHERE ORDERDETAILS.ORDERID =<BR>ORDERS.ORDERID"<BR><BR>Please, I need to solve this.

2. Senior Member
Join Date
Dec 1969
Posts
189

## RE: Is this a function= (very urgent)

I&#039;m not sure you really need to store the total cost in a separate field - you could just write a stored procedure that returns it. <BR><BR>But if you must do it that way, I would write a trigger ON INSERT, DELETE that modifies that field accordingly.

3. Senior Member
Join Date
Dec 1969
Posts
96,118

The whole point of being able to do joins is to avoid the need for duplicating information! That TOTALCOST field *is* a duplication of data! And that makes it ripe for causing problems. <BR><BR>It&#039;s really much better to just use a SUM to generate the total, as needed, via a join between the two tables.<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
5,104

## Ahh.. but, why not?

I completely understand the point of a normalized database. Believe me - I really do.<BR><BR>But, for some things like that, wouldn&#039;t it be much more convienent to store TotalCost in the Order table and then just update it as needed? Because, once an order is place - there are only going to be a few occurances of this data changing.<BR><BR>So, yes, each time the data changes - you have to update this field. But, down the road, when building reports - it&#039;s easier to refer to that one field rather than doing the SUM() with a GROUP BY.<BR><BR>Oh, well. It&#039;s just my \$0.02.<BR><BR>-Doug

5. Junior Member
Join Date
Dec 1969
Posts
2

## RE: Ahh.. but, why not?

Totaly agree with you!<BR>I made solved it with a trigger.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•