MySQL: Writing trigger that uses custom function - Raspberry Pi Forums

i teaching myself mysql writing database craft business run, hosted on pi.

trying write mysql trigger that, upon update of row, call custom function (which works fine outside of trigger) , update column in table trigger on.

code: select all

begin  update candles  set can_materials_cost = (select calculatematerialcost(new.can_id)  candles.can_id = new.can_id);  end 

i've written basic audit style triggers upon update save old value different table, reason when trying use custom function receive error stating:

have error in sql syntax; check manual corresponds mysql server version right syntax use near 'where candles.can_id = new.can_id);
end' @ line 4

custom function performs:

code: select all

begin  -- function assumes raw wax cost 20kg bags - beware!  declare result decimal(10,2);  set result = (  (select calculatewaxcost(_can_id))  +  (select calculatedyecost(_can_id))  +  (select calculatescentcost(_can_id)));  return result;  end 

please can shed light on i'm going wrong?

many thanks.

looks error in trigger definition, not custom function. error message looks might have extraneous closing bracket.

raspberrypi