Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- 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
- 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
Add Comment
Please, Sign In to add comment