After updating Purchase table I want to automatically update my Stock Table

Question

I am developing Stock Management System. I have to tables like this.

tblPurchase Table

PurchaseId  |  ItemCode  |   ItemName    | Quantity |
-----------------------------------------------------
 1          |  IMT0003   | Dell Laptop   | 30       |
-----------------------------------------------------
 2          |  IMT0004   | Samsung Phone | 2        |
-----------------------------------------------------
 3          |  IMT0003   | Dell Laptop   | 5        |
-----------------------------------------------------

tblProductStock Table

    ItemCode  |   ItemName    | Quantity |
    --------------------------------------
    IMT0003   | Dell Laptop   | 35       |
    --------------------------------------
    IMT0004   | Samsung Phone | 2        |
    --------------------------------------

Now I want to doing something like this. now tblPurchase table Dell Laptop quantity is 35. And tblProductStock also same. Now I return 5 Dell laptop back. Now I want to Update my tblPurchase table PurchaseId 1 is 25 or PurchaseId 3 is to 0. Because I returned 5 item back. I don't care about which purchaseid. if I deduct from 30. its should be 25. or if I deduct form purchaseId 3 . its should be 0.

And my tblProductStock table also should be change according to the tblPurchase Table Quantities.Its mean now my tblProductStock table dell laptop must be 30. To achieve this target we can use update query like this.

ALTER TRIGGER [dbo].[update_productstock_table_when_updating_purchasequantity] 
ON [dbo].[tblPurchase]
AFTER UPDATE
AS
BEGIN  
    UPDATE ps
    SET Quantity = ps.Quantity
    FROM tblProductStock ps JOIN
         (SELECT i.ItemCode, SUM(quantity) as quantity
          FROM tblPurchase i
          GROUP BY i.itemCode
         ) i
         ON ps.ItemCode = i.ItemCode;
END 

But my problem is not this. this is work as expected. Imagine my tblPurchase Table dell laptop quantity is 30. I sell one laptop. not my tblProductStock table dell laptop is 29. not 30. Now I return 5 dell laptops form tblPurchase table. Now my purchase quantity should me 25. And my tblProductStock table must be 24. because I sold one item. to achieve this targer i can use following update statement.

UPDATE ps
    SET Quantity = i.quantity - ps.Quantity
    FROM tblProductStock ps JOIN
         (SELECT i.ItemCode, SUM(quantity) as quantity
          FROM tblPurchase i
          GROUP BY i.itemCode
         ) i
         ON ps.ItemCode = i.ItemCode;

But how can I using this two update query with trigger. if I use both is same trigger its get wrong value. because quantity is same in both table. i want to update tblProductStock table. but my tblProductStock table quantity is 29 and purchase is 30 . after i return 5 now my tblPurchase 25 and my tblProductStock should be 24. thats why i am asking this questions. Somebody can rewrite this code please send me. I try to solve this more than 100 times. still not solve this problem.

Answer 1

You can use something like this to update your Stock table.

CREATE TABLE #TempPurchase
(   ID INT IDENTITY (1, 1) ,
    ItemCode VARCHAR (10) ,
    Quantity INT );

CREATE TABLE #TempSold
(   ID INT IDENTITY (1, 1) ,
    ItemCode VARCHAR (10) ,
    Quantity INT );

CREATE TABLE #TempStock
(   ID INT IDENTITY (1, 1) ,
    ItemCode VARCHAR (10) ,
    Quantity INT );

INSERT INTO #TempPurchase ( ItemCode ,
                            Quantity )
VALUES ( 'IMT0003', 25 ) ,
       ( 'IMT0004', 2 ) ,
       ( 'IMT0003', 5 );

INSERT #TempSold ( ItemCode ,
                   Quantity )
VALUES ( 'IMT0003', 2 );

INSERT INTO #TempStock ( ItemCode ,
                         Quantity )
VALUES ( 'IMT0003', 30 ) ,
       ( 'IMT0004', 2 );


CREATE TABLE #FinalData
(   ID INT IDENTITY (1, 1) ,
    ItemCode VARCHAR (10) ,
    Quantity INT );

INSERT #FinalData ( ItemCode ,
                    Quantity )
       SELECT   ts.ItemCode ,
                ISNULL (( SUM (tp.Quantity) - ts2.Quantity ), ts.Quantity) AS quantity
       FROM     #TempStock AS ts
                LEFT JOIN #TempPurchase AS tp ON tp.ItemCode = ts.ItemCode
                LEFT JOIN #TempSold AS ts2 ON ts2.ItemCode = ts.ItemCode
       GROUP BY ts.ItemCode ,
                ts2.Quantity ,
                ts.Quantity;

UPDATE ts
SET    ts.Quantity = fd.Quantity
FROM   #FinalData AS fd
       INNER JOIN #TempStock AS ts ON ts.ItemCode = fd.ItemCode;

DROP TABLE #TempStock
DROP TABLE #TempPurchase
DROP TABLE #TempSold
DROP TABLE #FinalData