Update Column in Another Table Based off INT value Change using MySQL AFTER UPDATE Trigger

Question

Getting error after error. Basically I am trying to set a columns value to 1 in my products table automatically if upon update of the product_stock table the column available is greater than 0 (meaning, at least one in stock).

MPN is both a unique and foreign key in my products table, so as long a positive value in the column available in the table product_stock the in_stock value for the mpn in the products table should be set to 1.

Two tables I'm working with:

1

CREATE TABLE `products` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `mpn` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `in_stock` int(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `mpn` (`mpn`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

2

CREATE TABLE `product_stock` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `mpn` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `size` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `available` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `product_stock_ibfk_1` (`mpn`),
 CONSTRAINT `product_stock_ibfk_1` FOREIGN KEY (`mpn`) REFERENCES `products` (`mpn`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And one variation of my trigger

DELIMITER $$

    CREATE TRIGGER ps_update AFTER UPDATE ON `product_stock`
    FOR EACH ROW BEGIN
      IF NEW.available > 0 THEN
            SET products.in_stock = 1;
      ELSE
            SET products.in_stock = 0;
      END IF;
    END$$

DELIMITER ;

Error code

1193 - Unknown system variable 'in_stock'

Answer 1

You cannot update value in another table using SET alone. You need to use proper UPDATE statement to do so.

I have also added more conditions, so that it does not fire UPDATE query every time. It will fire UPDATE only when there is a change in the in_stock value required.

DELIMITER $$
CREATE TRIGGER ps_update AFTER UPDATE ON `product_stock`

    FOR EACH ROW BEGIN

      -- update only when there is a change in the available
      IF NEW.available <> OLD.available THEN 

        -- update only when item becomes in_stock
        IF NEW.available > 0 AND OLD.available <= 0 THEN 
            UPDATE products 
            SET products.in_stock = 1 
            WHERE products.mpn = NEW.mpn;

        -- update only when item becomes out_stock
        ELSEIF NEW.available <= 0 AND OLD.available > 0 THEN 
            UPDATE products 
            SET products.in_stock = 0 
            WHERE products.mpn = NEW.mpn;
        END IF;
      END IF;
   END $$
DELIMITER ;