Hi my friends!
Last week, we needed to add an action that could be thrown when a product was published on Virtuemart 2.x, for that reason a good solution was to implement a trigger in MySQL.
At the beginning it seemed to be something simple and fast, but no at all, I spent more that an hour to end it, so today I would like to share a small code to create an on-update trigger in MySQL and modify an specific field (column table).
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DROP TRIGGER IF EXISTS `after_publish_product`; DELIMITER // CREATE TRIGGER `after_publish_product` BEFORE UPDATE ON `prefix_bd_virtuemart_products` FOR EACH ROW BEGIN IF NEW.published=1 AND NEW.published_on='0000-00-00 00:00:00' THEN SET NEW.published_on = NOW(); END IF; IF NEW.product_in_stock<=0 THEN SET NEW.published = 0; END IF; END // DELIMITER ; |
This little SQL script sets published_on field to NOW (date and time when the trigger is executed) when the field published is true and no publish date exists (it means, when the product is published).
By the way, Virtuemart 2.x does NOT have a published_on field, we had to add it to the products table.
That’s it!
Be happy with your code!
Alex Arriaga 
Thanks!
I found your article while searching for a similar situation and this is exactly what i am looking for. The only difference is that i need the trigger to set the product_sku once a product is created. the product_sku will be generated according to the product’s category and product_id.
Hi Alex,
Great!
VirtueMart sometimes is a little confusing but once you understand how it works and its structure it is easier to modify it and adapt according to your requirements.
Regards.