User avatar
FeersumEndjinn
Posts: 148
Joined: Mon Nov 21, 2011 4:02 pm

MySQL: Writing trigger that uses custom function

Thu Nov 15, 2012 4:07 pm

I am teaching myself MySQL by writing a database for a craft business I run, hosted on my pi.

I am trying to write a MySQL trigger that, upon update of a row, will call a custom function (which works fine outside of the trigger) and update a column in the table the trigger is on.

Code: Select all

BEGIN 
UPDATE candles 
SET can_materials_cost = (SELECT calculateMaterialCost(NEW.can_id) 
WHERE candles.can_id = NEW.can_id); 
END 
I've written basic audit style triggers that upon update save the old value into a different table, but for some reason when trying to use my custom function I receive an error stating:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE candles.can_id = NEW.can_id);
END' at line 4

The custom function performs:

Code: Select all

BEGIN 
-- This function assumes that the raw wax cost is for 20kg bags only - 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 anyone shed some light on where I'm going wrong?

Many thanks.
Morrolan / FeersumEndjinn

"And the lord God said unto John, 'Come forth and receive eternal life', but John came fifth and won a toaster."

tufty
Posts: 1456
Joined: Sun Sep 11, 2011 2:32 pm

Re: MySQL: Writing trigger that uses custom function

Thu Nov 15, 2012 4:21 pm

Looks like an error in your trigger definition, not the custom function. The error message looks like you might have an extraneous closing bracket.

BlackJack
Posts: 288
Joined: Sat Aug 04, 2012 8:28 am
Contact: Website

Re: MySQL: Writing trigger that uses custom function

Fri Nov 16, 2012 1:02 pm

@FeersumEndjinn: Looks like the closing parenthesis for the SELECT expression is missing or rather moved to the end of the next line. Untested:

Code: Select all

BEGIN 
UPDATE candles 
SET can_materials_cost = (SELECT calculateMaterialCost(NEW.can_id))
WHERE candles.can_id = NEW.can_id; 
END

Code: Select all

while not self.asleep():
    sheep += 1

Return to “Other programming languages”

Who is online

Users browsing this forum: No registered users and 1 guest