MySQL Trigger Sum

I would like help with a before update trigger that conditionally sums the amount column where day and id match, and only display the sum where variable is ‘total’.

id | day | variable | amount
-: | :------ | :------- | :-----
1 | Monday | Total | null
1 | Monday | null | 1
1 | Monday | null | 2
1 | Monday | null | 3
1 | Tuesday | Total | null
1 | Tuesday | null | 1
1 | Tuesday | null | 2
1 | Tuesday | null | 3
2 | Monday | Total | null
2 | Monday | null | 1
2 | Monday | null | 2
2 | Monday | null | 3
2 | Tuesday | Total | null
2 | Tuesday | null | 1
2 | Tuesday | null | 2
2 | Tuesday | null | 3

Is there a way to control the sum function so that it won’t update each total unless an associated value has been updated? I.E I wouldn’t want the Monday total to recalculate when values change inside a Tuesday row, or for another ID.

Fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fc3939aa508002dab7f2af45611717cf

Answer

Note that MySQL has a restriction on updating other rows in the same table in a trigger according to the documentation.

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

If the following SQL statement will work

UPDATE tb1 t1
JOIN (
  SELECT id, day, SUM(amount) as total
  FROM tb1 t
  WHERE id = 1 AND day = 'Monday' AND variable is NULL
  GROUP BY id, day
) t2 ON t1.id = t2.id AND t1.day = t2.day AND t1.variable = 'Total'
SET t1.amount = t2.total;

Then its implementation inside the trigger will throw an error

You can’t specify target table ‘tb1’ for update in FROM clause

But you can do it without the trigger and create a view

SELECT 
  id, day, variable,
  CASE WHEN variable = 'Total' 
       THEN (
         SELECT SUM(amount) FROM tb1 t1 
         WHERE t1.id = t.id AND t1.day = t.day AND t1.variable IS NULL
       ) 
       ELSE amount
  END AS amount
FROM tb1 t  

Or, it is even possible to completely delete all rows with a Total and retrieve them using the WITH ROLLUP clause.

SELECT 
  id, 
  day,
  CASE WHEN amount IS NULL THEN 'Total' END AS variable,
  SUM(amount) AS amount
FROM tb1
GROUP BY id, day, amount
WITH ROLLUP
HAVING id IS NOT NULL AND day IS NOT NULL

db<>fiddle