Help with a trigger

[Darkmatter5]

Here's my tables

 

Progress_par

parent_idnamepercentage

1item166.67

2item280.00

 

Progress_chi

child_idnameparent_idpercentage

1child1190.00

2child21100.00

3child3280.00

4child4110.00

 

I need a trigger for updates or inserts in Progress_chi.  That takes the average percentages of each relevant child item to it's parent item and updates the parent items percentage to be the overall percentage.

 

So say a new child is added to Progress_chi "child5, 2, 50.00", item2's percentage should be updated to 65.00.

 

I'm not really sure where to start.

[fenway]

What about:

CREATE TRIGGER Progress_chi_ai 
AFTER INSERT ON Progress_chi
  FOR EACH ROW BEGIN
    UPDATE Progress_par SET percentage = ( SELECT AVG(percentage) FROM Progress_chi WHERE parent_id = NEW.parent_id ) WHERE parent_id = NEW.parent_id;
  END;

[josborne]

I am trying to do something very similar but I keep getting a syntax error when trying to create the trigger.

 

CREATE TRIGGER Val_Update_TR AFTER INSERT ON Unit_tbl
FOR EACH ROW 
BEGIN 
UPDATE Pred_tbl SET Val_Against = (

SELECT SUM( Unit_For ) / SUM( Unit_Against ) 
FROM Unit_tbl
WHERE Pred_ID = NEW.Pred_ID
)
WHERE Pred_ID = NEW.Pred_ID;
END;

 

 

It is probably something really dumb but i keep looking at it and can't figure it out.

[fenway]

Did you set the delimiter first?