SQL Server Performance Forum – Threads Archive
Help with a triggerGood Afternoon- I have an Equipment table that has prices. Some Equipment pieces are attachments that have a parent ID. EQ_ID EQ_Price EQ_ParentID EQ_PriceWithAttach I am trying to find the best way to update a column called PriceWithAttach. I need to aggregate the prices of equipment that has a parent, add that to the price of the parent and populate the priceWithAttach column. This needs to happen upon the update of the price field. I am thinking either a trigger, or a trigger that calls an SP for htis, but not sure how to formulate one for best performance. My first go round with an after update trigger resulted in some blocking.
As always, any help is appreciated. Thanks!
What if you do the calculation at the front end rather than keeping this value in a filed. —————————————-
You do not have to store the sum amount. You can at all times calculate the sum amount for the current values, using a subquery, or a UDF – whatever. You can also create a view to do the calculation grouped by parent ID, and use that view in your standard queries, or use a derived table. Only if you find that using the subquery, UDF or view causes poor performance, and after making sure that you have the right indexes in place for making those calculations go fast – then you might consider storing the sum amount in a column. One thing about updating a column for a sum amount, is that an UPDATE query is typically slower than a SELECT query for the same rowset. This is where you may well run into locking issues.
Thanks for the input guys, I am looking into different options at this time. Thanks again!
Indexing the view Adriaan mentioned may be the solution you are looking for.