0

i got 4 table which call addproduct, sellproduct, reportfoundproduct, productbalance

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

below is my trigger :


CREATE
DEFINER=`root`@`localhost`
TRIGGER `user`.`addtotal2`
AFTER INSERT ON `user`.`addproduct`
FOR EACH ROW
BEGIN
UPDATE productbalance SET Quantity_OnHand = (select sum(Product_OrderQuantity) from (select * from addproduct) as temp)- (select sum(Sale_Quantity) from (select * from sellproduct) as temp2 )+(select sum(BoL_Product_Quantity) from (select * from reportfoundproduct) as temp3)where Product_ID= productbalance.Product_ID;
END$$

But i face the problem that after i update the data, the value turn to NULL....
whats the problem ?????????? urgent ~~~~!!!

thanks !

Edited by jscer: n/a

2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by smantscheff
0

Try:

UPDATE productbalance 
SET Quantity_OnHand = (
  select sum(Product_OrderQuantity) from addproduct 
    where Product_ID= productbalance.Product_ID
- select sum(Sale_Quantity) from sellproduct 
    where Product_ID= productbalance.Product_ID
+ select sum(BoL_Product_Quantity) from reportfoundproduct
    where Product_ID= productbalance.Product_ID
)
where Product_ID= productbalance.Product_ID
;

Edited by smantscheff: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.