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