I have a table with payments where, in addition to the amount of the payment, the person’s balance is kept at the time of payment. I wanted to do the following with the help of a mysql trigger: when deleting a payment, the balance at the beginning of the subsequent transactions decreased by the amount of the payment being deleted.

CREATE TRIGGER `before_delete_payment` BEFORE DELETE ON `payment` FOR EACH ROW BEGIN UPDATE `payment` SET `value_before` = `value_before` - OLD.`amount` WHERE `time` > OLD.`time`; END

But, accordingly, an error occurs that the trigger cannot work with the table with which it is associated.

How can I do in this case? To implement all not in a DB, separate request?

  • The most correct solution would be not to store the calculated current balance at all, but to receive it on the fly when it is needed. If there are problems with access speed, then cache the current balance in a separate table. But at the beginning of each operation it would still be worth calculating. And it’s impossible to update the table the way you want it in MySQL - Mike
  • And by the way, in a similar table, I generally forbid the deletion of records, only adding. Did the wrong wiring - insert a record on the reversal. so and incorrect calculations are not possible in principle, everything happens atomically with one operation and the history of actions with balance is maintained, which is useful for debriefing - Mike
  • Thanks for the tips! I was thinking not to delete the erroneous payments, but to make a new posting with the type “payment cancellation”. With this scheme, there will be no unpredictable man’s minus. But then I will need to create a field like cancelled_payment_id in which the identifier link to the canceled payment will be stored. Or, another option is to create an isintyleted field of type tinyint (1), mark remote payments in it and, accordingly, do not take them into account. Which option do you think is better? - segan

1 answer 1

Perform payment deletion not by a simple request, but by a stored procedure. And in it, implement all the necessary logic. Template:

 CREATE PROCEDURE delete_payment(p_id bigint) BEGIN DECLARE p_time DATETIME; DECLARE p_amount CURRENCY; SELECT p.`time`, p.amount INTO p_time, p_amount FROM payment p WHERE p.id = p_id; UPDATE payment SET value_before = value_before - p_amount WHERE `time` > p_time; DELETE FROM payment WHERE `time` = p_time; END;

Well, it would be nice to wrap all this in a transaction - well, how can it fail what action ...

  • But in general, I absolutely agree with Mike - well, it's not a matter of dragging the calculated balance as static data. - Akina