当向表中插入新记录时，更新表

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

我认为SQL Server触发器可以帮助您解决这个问题。触发器是一种特殊类型的存储过程，当数据库服务器中发生事件时，它会自动运行。

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