Automatic insertion of calculated values to another

by AmeLisa   Last Updated April 20, 2018 14:06 PM

I am creating an application, so I need trigger or function that will help automatically enter data in the database as data is entered into the system considering the other table's primary key.

I currently have AddItems, Sales and Products tables. The AddItems table is where all the invoice data is stored. What I need is to be able to add the quantity of products together according to the Item_Code. Once the sum is found the data should be stored on the Products table. This should happen every time a product product is added in the AddItems table.

AddItems

Item_ID (PK, IDENTITY)
Date
Supplier_Name
Track_NO
Quantity
Item_Code
Item_Description
Vat
Amount
Total_Amount

Products

Product_Id (Identity)
Item_Code (PK)
Item_Description
Available_Quantity

Query I managed to come up with needs to be manually ran to insert the values. Below is the statement used.

INSERT INTO Products
(Item_Code, Item_Description, Available_Quantity)
SELECT Item_Code, Item_Description,
    Sum(Quantity)
FROM AddItems
Group By Item_Code, Item_Description;

(Asked the same question but i couldn't merge my account with the other)



Related Questions