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)