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.