How To Set Variables in MySQL Update Trigger

Question

I have an estimates table, and an estimates_line_items table. I am trying to update the estimates table whenever one it's child line items changes. I keep running into a syntax error. The error isn't very descriptive("You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version"). It appears I am screwing up at setting the variable values.

CREATE TRIGGER Update_estimate_from_line_items
AFTER UPDATE
 ON estimate_line_items FOR EACH ROW
BEGIN
 -- variable declarations
 DECLARE vPrev_amnt INT;
 DECLARE vNew_amnt INT;
 DECLARE nDiff INT;
 SET vPrev_amnt = OLD.price * OLD.quantity;
 SET vNew_amnt = NEW.price * NEW.quantity;
 SET nDiff = new_amnt - prev_amnt;
 -- trigger code
 UPDATE estimates SET 
 subtotal = total + nDiff,
 total = subtotal + (tax_rate/100 * subtotal)
 WHERE estimate_id = NEW.estimate_id;
END;

EDIT: I've also tried setting the variables like this, with the same results: SET vPrev_amnt := (SELECT OLD.price * OLD.quantity);

• Michael - sqlbot: You've omitted the most important of the error... for the right syntax to use near <something> at line x. What's the actual error? It may not make sense to you, but I assure you it explains exactly the problem, once you understand the logic that generates the error.

Answer 1

How about ...

drop trigger if exists Update_estimate_from_line_items;
delimiter //
CREATE TRIGGER Update_estimate_from_line_items
AFTER UPDATE
 ON estimate_line_items FOR EACH ROW
BEGIN
 -- variable declarations
 DECLARE vPrev_amnt INT;
 DECLARE vNew_amnt INT;
 DECLARE nDiff INT;
 SET vPrev_amnt = OLD.price * OLD.quantity;
 SET vNew_amnt = NEW.price * NEW.quantity;
 SET nDiff = vNew_amnt - vPrev_amnt; -- names amended
 -- trigger code
 UPDATE estimates SET 
 subtotal = total + nDiff
 , total = subtotal + (tax_rate/100 * subtotal)
 WHERE estimate_id = NEW.estimate_id; 
END//
delimiter ;

• gkephorus: Might be helpful to explain what you changed outside of the code example. It took me a while to see the difference ;-) Thanks for the confirmation that this is the way to do things.