Mysql trigger if statement update second table

Question

I am trying to create a mysql trigger. This is what I am trying to create, but somehow it tells me that: "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 'A FOR EACH ROW".

What Am I doing wrong here? I have tried to naming them differently, but it does not seem to work.

delimiter //
CREATE TRIGGER stock_update BEFORE UPDATE ON `stock` A
       FOR EACH ROW
       BEGIN
           IF NEW.`quantity` < 1 THEN //belongs to alias A
               UPDATE `ps_product` B
               SET B.`visibility` = 'none';
               WHERE id_product = OLD.id_product  //Should be the id_product of A alias
           ELSEIF NEW.quantity > 0 THEN
               UPDATE `ps_product` C
               SET C.`visibility` = 'both';
               WHERE id_product = OLD.id_product //Should be the id_product of A alias
           END IF;
       END;//
delimiter ;

What do you expect the "A" at the end of your second line to do? The comments in the code suggest you're thinking of it as an alias. You can not alias the table the trigger is on there. Also, MySQL comments start with # or --. — Uueerdo
– Uueerdo, Commented Oct 4, 2019 at 23:21

nbk · Accepted Answer · 2019-10-04 23:14:44Z

1

Try this one

delimiter //
CREATE TRIGGER stock_update BEFORE UPDATE ON `stock` 
       FOR EACH ROW
       BEGIN
           IF NEW.`quantity` < 1 THEN #belongs to alias A
               UPDATE `ps_product` B
               SET B.`visibility` = 'none'
               WHERE id_product = OLD.id_product;  #Should be the id_product of A alias
           ELSEIF NEW.quantity > 0 THEN
               UPDATE `ps_product` C
               SET C.`visibility` = 'both'
               WHERE id_product = OLD.id_product; #//Should be the id_product of A alias
           END IF;
       END;//
delimiter ;

This shows no errors in mysql workbbench

answered Oct 4, 2019 at 23:14

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql trigger if statement update second table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related