CODEWITHSUNDEEP
mysqltriggers
simon
simon

Reputation: 2377

Mysql trigger if statement update second table

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 ;

Upvotes: 0

Views: 39

Answers (1)

nbk
nbk

Reputation: 49373

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

Upvotes: 1

Related Questions