1

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

1
  • 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. Aug 13, 2017 at 0:33

1 Answer 1

2

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 ;
1
  • 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.
    – gkephorus
    Nov 9, 2021 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.