c# - Update tblProductStock table when Insert New record to tblPurchase table

Question

ALTER PROCEDURE [dbo].[spPassPurchaseDataToProductStock]
AS
BEGIN
    SELECT MAX(ItemName) ItemName, ItemCode, SUM(Quantity) Quantity
    INTO #tmp
    FROM tblPurchase
    GROUP BY itemCode;

    UPDATE tblProductStock
    SET Quantity = #tmp.Quantity
    FROM #tmp 
    WHERE ItemCode = #tmp.ItemCode

    INSERT INTO tblProductStock (ItemName, ItemCode, Quantity)
        SELECT ItemName, ItemCode, Quantity 
        FROM #tmp 
        WHERE ItemCode NOT IN (SELECT ItemCode FROM tblProductStock)

    DROP TABLE #tmp;
END

Answer 1

I think that SQL Server Trigger Can help you to solve this problem. A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.

   CREATE TRIGGER <TriggerName>  
   ON ProductStock  
   AFTER INSERT | UPDATE 
   AS  { sql_statement }