How to create an “on update” trigger in MySQL to modify Virtuemart 2.x fields

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

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!