clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805438 fiddles created (40834 in the last week).

CREATE TABLE AddItems ( Item_ID int IDENTITY PRIMARY KEY, Quantity int NOT NULL, Item_Code varchar(10) NOT NULL, Item_Description varchar(100) NOT NULL ); CREATE TABLE Products ( Product_Id int IDENTITY PRIMARY KEY, Item_Code varchar(10) NOT NULL, Item_Description varchar(100) NOT NULL, Available_Quantity int NOT NULL DEFAULT(0) );
 hidden batch(es)


CREATE TRIGGER trg_AddItems_Insert ON AddItems FOR INSERT AS BEGIN /* update existing products */ UPDATE p SET Available_Quantity = Available_Quantity + Qty FROM Products p JOIN (SELECT Item_code, SUM(Quantity) Qty FROM inserted GROUP BY Item_Code) s ON p.Item_Code = s.Item_Code; /* insert new products */ INSERT INTO Products (Item_Code, Item_Description, Available_Quantity) SELECT i.Item_Code, i.Item_Description, Sum(Quantity) FROM inserted i WHERE NOT EXISTS(SELECT 1 FROM Products WHERE Item_Code = i.Item_Code) GROUP BY i.Item_Code, i.Item_Description; END
 hidden batch(es)


INSERT INTO AddItems VALUES (10, 'AAA', 'PRODUCT A'), (5, 'AAA', 'PRODUCT A'), (7, 'CCC', 'PRODUCT C'), (1, 'BBB', 'PRODUCT B'); INSERT INTO AddItems VALUES (25, 'AAA', 'PRODUCT A'), (10, 'BBB', 'PRODUCT B'), (3, 'CCC', 'PRODUCT C'), (5, 'CCC', 'PRODUCT C');
14 rows affected
 hidden batch(es)


SELECT * FROM PRODUCTS;
Product_Id Item_Code Item_Description Available_Quantity
1 AAA PRODUCT A 40
2 BBB PRODUCT B 11
3 CCC PRODUCT C 15
 hidden batch(es)