Update stock with trigger

0

I have 2 tables, a "Products" table composed of the following

CREATE TABLE PRODUCTS (
    id_product BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(6) NOT NULL UNIQUE,
    name VARCHAR(30) NOT NULL,
    price FLOAT(6,2) UNSIGNED NOT NULL,
    is_discounted TINYINT(1) NOT NULL,
    discount_percentage INT(2) NOT NULL,
    discounted_price FLOAT(6,2) NOT NULL,
    description TEXT NOT NULL,
    stock INT(4) UNSIGNED NOT NULL,
    photo_1 VARCHAR(50),
    product_type_id BIGINT(4) UNSIGNED NOT NULL,
    brand_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (product_type_id) REFERENCES product_types (id_product_type),
    FOREIGN KEY (brand_id) REFERENCES brands (id_brand),
    is_visible TINYINT(1) NOT NULL
)
ENGINE = INNODB
CHARSET= UTF8
COLLATE= utf8_unicode_ci

And a table of the sale

CREATE TABLE PRODUCT_SALES (
    id_product_sale BIGINT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    QUANTITY INT(4) UNSIGNED NOT NULL,
    sale_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_price FLOAT(12,2) UNSIGNED NOT NULL,

    user_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (user_id) REFERENCES USERS(id_user),

    product_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (product_id) REFERENCES PRODUCTS(id_product),

    payment_method_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (payment_method_id) REFERENCES PAYMENT_METHODS(id_payment_method),

    sale_status_id BIGINT(4) UNSIGNED NOT NULL,
    FOREIGN KEY (sale_status_id) REFERENCES SALE_STATUS(id_sale_status)

)
ENGINE = INNODB
CHARSET= UTF8
COLLATE= utf8_unicode_ci

I need to make a trigger that updates the stock of the products when making a purchase. I have seen in responses from this forum and others that do something very similar to what I try using a "NEW", but for some reason I throw an error in the line that I use this, so I tried to implement it from the next way but it does not work for me.

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'm using MariaDB. Best regards.

    
asked by Lucas. D 23.05.2018 в 01:02
source

0 answers