mysql double trigger two updates together

Question

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`)

Lord Elrond · Accepted Answer · 2019-03-09T01:29:41.097

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.

mysql double trigger two updates together

1 Answers1