Recursive Updates on large table I am desperately seeking a solution for a problem, which i will try to fully describe hoping that a somebody can come up with a clever solution.

The metadata : 

At the moment i have the following set of tables (minified to remove chatter) :



Table "Recipe" has a Foreign Key to table "Item" on Recipe.OutputITemId = Item.Id

Table "RecipeIngredient" has a Foreign Key to table "Item" on RecipeIngredient.ItemId = Item.Id

Table "RecipeIngredient" has a Foreign Key to table "Recipe" on RecipeIngredient.RecipeId = Recipe.Id

A recipe contains a number of ingredients that represent items. The items representing these ingredients could again be produced by a recipe. This creates a structure of recipe's and their ingredients.

At the moment the list of recipes is about 8000, the list of ingredients about 25000. I have written a recursive CTE that returns the tree structure resulting in 144000 rows. A fair amount of recipe's are reused.

The problem : Calculating the total cost. 

The cost of a recipe has to be calculated based on the total cost (SUM SellValue) of all the descendant recipes or, when no recipe available for the current item, the SellValue of that particular item. 
On stale data, traversing through a CTE with a cursor, these values could easily be updated, though this is not an option IMHO. The reason for this is that the SellValue of an item is updated VERY frequently, read constantly. An update of a certain item would cause numerous recipe's to have their pricing recalculated (up to 100 recipe's).

I have tried using recursive triggers, even though i hate em, on Item and Recipe updating the recipe based on its descendants, to no good effect. 

Apparently my knowledge of SQL, plus searching Google for quite a while, didn't provide a feasible solution. 

Please help, i'm pulling out the few hairs i have left... ;)

UPDATE 1:

The use of nested/recursive triggers on the recipe table led to a common problem, deadlocks. For now i'll investigate the possibility of using a recursive CTE with SUMS on the respective values.
Your schema diagram is good. You should not put any total cost column either to Recipe or RecipeIngedient as it is not compliant to normalization. And so there is no need for triggers.
The total cost sum should be calculated on the fly in a query that you use to list recipes.