Scribd
Upload
20 views1 page

Stock Script

The document outlines the creation of a 'demo_stocks' table with various attributes and foreign key constraints. It includes triggers for automatically updating stock quantities in response to new product entries and purchase or sale transactions. The triggers ensure that stock levels are adjusted accordingly after insert operations in related tables.

Uploaded by

isdbrcl
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
20 views1 page

Stock Script

The document outlines the creation of a 'demo_stocks' table with various attributes and foreign key constraints. It includes triggers for automatically updating stock quantities in response to new product entries and purchase or sale transactions. The triggers ensure that stock levels are adjusted accordingly after insert operations in related tables.

Uploaded by

isdbrcl
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

CREATE TABLE demo_stocks(

product_id NUMBER(5),
product_name VARCHAR2(50),
category VARCHAR2(30),
supp_id NUMBER(8) REFERENCES demo_suppliers(supp_id),
product_qty NUMBER(6) DEFAULT 0
);

ALTER TABLE demo_stocks


ADD FOREIGN KEY(product_id)
REFERENCES demo_product_info(product_id);

INSERT INTO demo_stocks(product_id, product_name, category)


SELECT product_id, product_name, category FROM demo_product_info;

CREATE OR REPLACE TRIGGER product_stock_update


AFTER INSERT ON demo_product_info
FOR EACH ROW
BEGIN
INSERT INTO demo_stocks(product_id, product_name, category)
VALUES(:NEW.product_id, :NEW.product_name, :NEW.category);
END;
/

CREATE OR REPLACE TRIGGER pur_stock_update


AFTER INSERT ON demo_purchase_items
FOR EACH ROW
BEGIN
UPDATE demo_stocks
SET product_qty= product_qty+:NEW.item_qty
WHERE PRODUCT_ID = :NEW.PRODUCT_ID;
END;
/

CREATE OR REPLACE TRIGGER sale_stock_update


AFTER INSERT ON DEMO_ORDER_ITEMS
FOR EACH ROW
BEGIN
UPDATE demo_stocks
SET product_qty= product_qty-:NEW.QUANTITY
WHERE PRODUCT_ID = :NEW.PRODUCT_ID;
END;
/

You might also like