I'm trying to make a trigger to automate an issue about categories of a product.
I'm experienced in MSSQL but not in MySQL sintaxis, so I'm having some problems in writing it... I someone could please help me with this trigger on products table, I would really apreciate it
I'm checking after update a product quantity if this product exists or not in my category "In stock" (id=91 in my case)
If qty<=0 the check if the product was in id=91, and delete it from that category in that case.
On the other hand, if qty>0 check if that product exists in category id=91 and if not, insert into that category.
Here you have the code:
Code: Select all
use ocar699; CREATE TRIGGER auto_stock_category AFTER UPDATE ON ocar699.oc_product for each row begin if EXISTS (SELECT * FROM oc_product_to_category WHERE product_id = OLD.product_id) THEN DELETE FROM `oc_product_to_category` WHERE product_id=OLD.product_id and category_id=91 ; else if NOT EXISTS (SELECT * FROM oc_product_to_category WHERE product_id = OLD.product_id) THEN INSERT INTO `oc_product_to_category` (`product_id`, `category_id`) VALUES (OLD.product_id, '91'); end if end I have errors in line 7,9 and 13... Thank you!!