MY SQL Trigger balance change only one field

Question

I have 4 tables: addproduct, sellproduct, reportfoundproduct, productbalance

I want to make a trigger to update the balance quantity of stock in productbalance table after insert data to the other three tables (addproduct, sellproduct, reportfoundproduct)

Below is my trigger in sellproduct table:

CREATE
DEFINER=`root`@`localhost`
TRIGGER `user`.`bad1`
AFTER INSERT ON `user`.`sellproduct`
FOR EACH ROW
BEGIN
        
UPDATE productbalance 
SET Quantity_OnHand =(Quantity_OnHand- (select (sell_Quantity) from (select * from sellproduct) as temp3)) 
WHERE Product_ID= productbalance.Product_ID ; 
     
END$$

It means quantity balance = add product - sales product

But when the trigger is executed, it changes my whole row data - sales product...

How can I just change the product ID that changed in value?

Answer 1

You need to use the NEW psuedo table so you only use the information in the new row

CREATE DEFINER=`root`@`localhost` TRIGGER `user`.`bad1`
AFTER INSERT ON `user`.`sellproduct` FOR EACH ROW
BEGIN

UPDATE productbalance 
SET Quantity_OnHand = Quantity_OnHand - NEW.sell_Quantity
WHERE Product_ID = NEW.Product_ID ; 

END$$