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.
Item_ID (PK, IDENTITY) Date Supplier_Name Track_NO Quantity Item_Code Item_Description Vat Amount Total_Amount
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)