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);

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 ;