MySQL Trigger – Error Trying to Update Stock

mariadbMySQLtrigger

I have bascially 2 tables

CREATE TABLE PRODUCTS (
    id_product BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(6) NOT NULL UNIQUE,
    name VARCHAR(30) NOT NULL,
    stock INT(4) UNSIGNED NOT NULL
);

CREATE TABLE PRODUCT_SALES (
    id_product_sale BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    QUANTITY INT(4) UNSIGNED NOT NULL,
    product_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (product_id) REFERENCES PRODUCTS(id_product)
);

And a trigger

CREATE TRIGGER updateStock
AFTER INSERT
ON PRODUCT_SALES
FOR EACH ROW
    UPDATE PRODUCTS
    SET PRODUCTS.stock = PRODUCTS.stock - PRODUCT_SALES.quantity
    WHERE PRODUCTS.id_product = PRODUCT_SALES.product_id

I want to update the stock of X product when someone makes an instert in product_sales table, but i get the following error when inserting a row in my product_sales table

Unknown column 'PRODUCT_SALES.product_id' in 'where clause'

I'm useing MariaDB Ver 15.1 Distrib 10.2.12-MariaDB, for Win64 (AMD64).

I've made a DBFiddle.

Thanks in advance, best regards.

Best Answer

You cannot use the reference PRODUCT_SALES. for columns inside the trigger.

In the code, once you get passed FOR EACH ROW you must reference the new value with NEW.

CREATE TRIGGER updateStock
AFTER INSERT
ON PRODUCT_SALES
FOR EACH ROW
    UPDATE PRODUCTS
    SET PRODUCTS.stock = PRODUCTS.stock - NEW.quantity
    WHERE PRODUCTS.id_product = NEW.product_id;
Related Question