Member Avatar for jscer

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 because: n/a
  • 2 Contributors
  • 1 Reply
  • 192 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for smantscheff

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 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.