sql - Database trigger to update a row on parent table after child table has been updated -

first time creating database trigger. have child table when cost column updated, need parent table update cost column reflect change.

here sorry attempt far. that's not working. having problem figuring out how extract total cost variable , store in parent table.

my current approach assumes static id vaule @ moment. not entirely sure how dynamically determine id value of row updated.

create trigger parentcost_update on childtable after insert, update    select sum(cost) totalcost childtable parent_id=2080  update parenttable     set cost=totalcost     id=parent_id;  go 

this current script returning error

msg 207, level 16, state 1, procedure parentcost_update, line 9 invalid column name 'totalcost'.

you need careful in triggers, in there more 1 row updated. therefore, need row-based handling.

to obtain newly inserted / updated row, use inserted , deleted pseudo rows.

you going need implement deleted trigger well, viz, if row removed child table parent need recalculated.

here's row based take, using cte map two-step process above , follows:

create trigger parentcost_update on childtable after insert, update, delete     set nocount on;    cteparentsaffected    (       select ins.parent_id       inserted ins        union        select del.parent_id       deleted del    )    , ctetotal    (       select ct.parent_id, sum(ct.cost) totalcost        childtable ct       inner join cteparentsaffected par       on ct.parent_id = par.parent_id       group ct.parent_id    )   update pt       set cost=cte.totalcost       parenttable pt       inner join ctetotal cte       on id=cte.parent_id; go 

with sqlfiddle here