MySQL トリガで同一テーブルを更新する

預金通帳の「現在高」にあたる情報を保存すべくカラムを作った。
パフォーマンス的な観点から。

INSERTがメインのログ的なテーブルなので、INSERTする都度アプリ側のモデルで集計すればいいのだが、MySQL側のトリガでの処理がベストかと思い実装する。

略　このトリガはINSERT時にエラーがでます

CREATE TRIGGER trigger_ledgers_insert
AFTER INSERT
ON ledgers FOR EACH ROW
BEGIN

SELECT SUM(quantity) INTO @sum
 FROM ledgers;
UPDATE ledgers SET
 quantity_current = @sum
 WHERE id = NEW.id;

END

略

ところが

#1442 - Can't update table 'ledgers' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

ストアドファンクションまたはトリガーは、そのストアドファンクションまたはトリガーを呼び出したステートメントによって (読み取りまたは書き込みに) すでに使用されているテーブルを変更できません。

https://dev.mysql.com/doc/refman/8.0/ja/stored-program-restrictions.html#stored-routines-trigger-restrictions

SQLServerの時は気にせず書いていたような覚えがあるが…

で、ナイスな方法ありました。

DELIMITER //

CREATE TRIGGER trigger_ledgers_insert
BEFORE INSERT
ON ledgers FOR EACH ROW
BEGIN

SELECT SUM(quantity)+NEW.quantity INTO @sum
 FROM ledgers;
SET NEW.quantity_current = @sum;

END//

DELIMITER ;

ポイントは BEFORE の NEW を更新すること。
テーブルには書き込めないから、書き込む前のNEWの内容を更新しよう という方針。

BEFOREのNEW ということはこの集計時点ではまだテーブルには書き込まれていません。
したがってSUMに含まれないため、+NEW.quantity をしています。