Can’t figure out issue with my trigger logic?

I have two tables, listsalesorders and listinvoices. listsalesorders is the parent table, and each sales order may have many invoices, but each invoice is only attached to a single sales order.

In my listsalesorers, I have a column that keeps track of how many of the invoices are in progress, which is based on whether the invoice has been sent out.

In an after update trigger on the listinvoices table, I want to update the parent sales order row. Right now I have this

DELIMITER //
CREATE TRIGGER listinvoices_AUPD AFTER UPDATE ON listinvoices
FOR EACH ROW
BEGIN
UPDATE listsalesorders as so
SET so.invoicesInProgress = (SELECT COUNT(i.idx) FROM listinvoices i WHERE new.parentSOId = so.idx AND (i.sentToContact = 1 or i.sentToAccounting=1))
WHERE so.idx = new.parentSOId;
END//
DELIMITER ;

Before there was a stored procedure that was updated every single sales order even if just a single invoice was updated, which meant 30 seconds to just update a single invoice. This was my attempt to only update the exact parent sales order. However when I did a test for speed and ran something that affected all invoice id’s %5 = 0, I discovered something odd, that almost all my sales orders affected calculated 382 invoicesInPorgress.

What am I doing wrong? What’s the right way to only update the parent row of a child row on trigger? I understand you may want to say just use a view, however this column is referenced in many places in our client application already and it would not be an easy fix to change all those queries, it’d be much easier for me to just fix how the column is calculated.

Answer

Seems wring condtion in the subquery for the COUNT, try this:

DELIMITER //
CREATE TRIGGER listinvoices_AUPD AFTER UPDATE ON listinvoices
FOR EACH ROW
BEGIN
UPDATE listsalesorders as so
SET so.invoicesInProgress = (SELECT COUNT(i.idx) FROM listinvoices i WHERE i.parentSOId=new.parentSOId AND (i.sentToContact = 1 or i.sentToAccounting=1))
WHERE so.idx = new.parentSOId;
END//
DELIMITER ;