Ufdjrw

After trying to create a new trigger in invoices table to UPDATE `invoices` SET invoices.`owes` = (`owes` - `paid`);

I get an error because I already that another trigger in payments that is updating. (see below)

I'm looking to keep the existing trigger below, but how to modify it to also update owes to (owes - paid) in the invoices table.

CREATE TRIGGER `after_payment_update` AFTER UPDATE 
 ON `payments`
 FOR EACH ROW UPDATE `invoices` 
 SET invoices.`paid` = (SELECT SUM(payments .`payment`) 
 FROM payments WHERE payments.`invoice` = invoices.`invoice`)

You can't create a second trigger that "triggers" on the same action as another trigger. Instead you would use a DELIMITER $$ statement like below and fill your trigger with all the relevant code you want executed.

DELIMITER $$
CREATE TRIGGER after_update_payments
AFTER UPDATE ON payments
FOR EACH ROW BEGIN
UPDATE invoices
 SET NEW.paid = (SELECT SUM(payment) FROM payments WHERE invoice = NEW.invoice),
 NEW.owes = (owes -(SELECT SUM(payment) FROM payments WHERE invoice = NEW.invoice));
END $$
DELIMITER ;

You don't actually need a DELIMITER in the trigger above, so I will show you an example where you would need to use it:

DELIMITER $$
CREATE TRIGGER after_update_payments
AFTER UPDATE ON payments
FOR EACH ROW BEGIN
 IF (some condition here) THEN
 UPDATE invoices
 SET NEW.paid = (SELECT SUM(payment) FROM payments WHERE invoice = NEW.invoice),
 NEW.owes = (owes -(SELECT SUM(payment) FROM payments WHERE invoice = NEW.invoice));
 END IF;
END $$
DELIMITER ;

As a general rule, if you need to execute multiple statements that need a ; at the end of them, you need to use a DELIMITER. If this still doesn't make sense, a great explanation for delimiters can be found here.

Now, on a side-note, I don't think this approach is the most optimal one. What I would do in your situation is create a view that combines these tables. For example:

CREATE 
ALGORITHM = UNDEFINED 
DEFINER = `root`@`localhost` 
SQL SECURITY DEFINER
VIEW invoice_payments_view AS (

 SELECT 
 t1.*,
 SUM(t2.payment) as amount_paid,
 SUM(t2.owes - SUM(t2.payment)) as amount_owed
 FROM invoices t1
 JOIN payments t2 ON (t1.invoice=t2.invoice)
 GROUP BY t1.invoice
)

Then to access the amount_paid and amount_owed columns, you would simply query the following:

SELECT invoice, amount_paid, amount_owed FROM invoice_payments_view 
WHERE invoice=1;

Note that I am by no means an expert on this topic, and I am only showing you how I would approach this situation. Also, I didn't test any of this code, so you might need to modify it slightly. If you have any issues let me know and I can update.