トリガーで計算させた値を履歴として記録するには

質問

株の購入履歴をトリガー内部で計算したいと考えています。
buying_stockの購入をすると、トリガーで最新の数量を計算しnow_stockテーブルに最新情報を記入、更新します。

テーブル
●buying_stock
d1(プライマリ),d2_usr_id,d3_stock_id,d4_buy_stock_volume,
●now_stock
f2_stock_id(プライマリ),f3_stock_name,f4_latest_stock_volume

トリガ
DELIMITER //
CREATE TRIGGER a_ref0 BEFORE INSERT ON buying_stock
FOR EACH ROW BEGIN
INSERT INTO now_stock SET f2_stock_id=NEW.d3_stock_id,f3_stock_name=f3_stock_name,f4_latest_stock_volume=f4_latest_stock_volume+NEW.d4_buy_stock_volume
ON DUPLICATE KEY UPDATE f2_stock_id=NEW.d3_stock_id,f3_stock_name=f3_stock_name,f4_latest_stock_volume=f4_latest_stock_volume+NEW.d4_buy_stock_volume;
END;
//
DELIMITER ;

ここまでは出来ました。
ただ、これでは最新価格は残りますが、購買履歴が残りません。
そこで、下記のトリガーを作成しました。

テーブル
●stock_history
g1(プライマリ),g2_stock_id,g3_stock_name,g4_latest_stock_volume

トリガー
DELIMITER //
CREATE TRIGGER a_ref000 BEFORE INSERT ON now_stock
FOR EACH ROW BEGIN
INSERT INTO stock_history SET g2_stock_id=NEW.f2_stock_id,g3_stock_name=NEW.f3_stock_name,g4_latest_stock_volume=NEW.f4_latest_stock_volume;
END;
//
DELIMITER ;

しかしながら、g3_stock_nameとg4_latest_stock_volumeがNULLになってしまいます。
古い購入履歴と最新の購入数は別物として捉えられているようです。
一方、同一のものとして指示するために、テーブルをg2_stock_id(プライマリ),g3_stock_name,g4_latest_stock_volumeとすると、Duplicate違反を指摘されます。

トリガーで計算した後の履歴を残すためにはどのような方法があるでしょうか?

テーブルには
●stock_name
a1(プライマリ),a2_stock_id,a3_stock_name,
というものもございますので、これを使用すれば出来るのかもしれません。

ご指導いただきたくお願いいたします。

回答No.2

Siegrune · ベストアンサー

## なにもかもトリガーでやるのがいいとは思いませんが。
## ということで蛇足というか前提を先に。

まず、累計の履歴は、
select sum(d4_buy_stock_volume) from buying_stock
where d2_usr_id = '入力されたUSER ID'
and d3_stock_id = '入力された株のID'
and d5_取引日（テーブル構成にないけどないと困るはず） <= '入力された日付'
で取得できるはずです。
・・・テーブルに持つ必要はないはずということです。
↓
しかし、画面上に日付別に一覧を出したときに、いちいちこのselect文を実行するのは処理が遅くなる
ということであえて、テーブルを作りたいということなら、作ってもいいとは思います。
ただし、buying_stockに該当する項目を持ってもかまわないと思いますが。

で、持つとどうなるか。
　例　 　1/18 +3000株　累計3000株
　　 　　1/19 +5000株　累計8000株
　　 　　1/20 +5000株　累計13000株
　　 　　1/21 -2000株　累計11000株
で、1/19のデータを+2000株に変更すると、
　例　 　1/18 +3000株　累計3000株
　　 　　1/19 +2000株　累計5000株←変更
　　 　　1/20 +5000株　累計10000株←変更
　　 　　1/21 -2000株　累計9000株←変更
1/19以後の全レコードを修正しないとだめです。
つまり入力時に負荷をかけて、出力時の負荷を軽減していると思ってください。
（入力が非常に多く出力が少ないならしないほうがいいということです。
　但し、入力時は、1件処理するだけだが、出力時は全件するので全体として処理速度向上に
　成功することも多々あります。）

さて、本題ですが、
修正・削除がない（常に最新の日付で処理を行う。つまり、修正は、差の数量を最新日付で入力。
削除は、-の数量を最新日付で入力）というのなら、
now_stockにトリガーを作るほうが簡単。
★質問文にあるトリガーのプログラムがそうですね。
（上記前提が違うなら、かなり変更必要。・・・補足かお礼にでも書いてください。）
但し、データベースがトリガーで登録・更新・削除されたレコードに対してトリガーが起動できること
が条件となりますが。
(Oracleはできる。SQL ServerはDBの設定によりけり。MySQLは調べていませんけど)
・・・動いていそうなのでこの条件はＯＫなのでしょう。

ただし、トリガーからトリガーを起動するのは、システムが複雑になるので
決してお薦めではありませんが。

で、質問の回答ですが、
>g3_stock_nameとg4_latest_stock_volumeがNULLになってしまいます。
NULLになりますか？（ならない気もするけど・・・）
ひょっとしたら、
CREATE TRIGGER a_ref0 BEFORE INSERT ON buying_stock
でinsertかupdateして
CREATE TRIGGER a_ref000 BEFORE INSERT ON now_stock
を呼び出しているからかもしれません。
BEFORE INSERTなので、buying_stockのInsert前の状態で、a_ref000が呼び出されます。
ということは、now_stockのNEW.xxxは不定になってしまうということかもしれません。

両方とも、AFTER INSERTにして試してみてください。

なお、INSERT INTO ... ON DUPLICATE KEY UPDATE ... でトリガーを起動しているので
BEFORE INSERTなら、ON DUPLICATE KEYであろうがなかろうが実行されるようですが、
AFTER INSERTは、ON DUPLICATE KEYの時には実行されません。
したがって、同じ内容で、AFTER UPDATEでもう一つ作る必要があります。
（MySQLは、On Insert,Update・・・がかけない様子。Versionによるのかもしれませんが。）

あと、蛇足ですが、
・・・
INSERT INTO stock_history SET g2_stock_id=NEW.f2_stock_id,g3_stock_name=NEW.f3_stock_name,g4_latest_stock_volume=NEW.f4_latest_stock_volume;
END
としていると
INSERT INTO now_stock SET　・・・ON DUPLICATE KEY UPDATE ・・・
で、ON DUPLICATE KEYのほうで呼び出されたときに、２個レコードができてしまうと思いますが。
日付とstock_idでユニークキーを作るかプライマリキーにしてon duplicate書いたほうが
いいと思います。

お礼

お世話になっております。
下記で解決出来ましたのでご報告いたします。
a_ref000のトリガは、aref0トリガの指示でupdateしたnow_stockをベースに動かそうとしているにも関わらず、aref000に与えた指示が「insertで稼動するよう」していたのが不調の原因でした。
ご指導頂きましたafterを使用し、指示をupdate時実行とし、さらに、「g1 をauto_increment、primarykey」
とすることにより解決致しました。

DELIMITER //
CREATE TRIGGER a_ref000 AFTER UPDATE ON now_stock
FOR EACH ROW BEGIN
INSERT INTO stock_history SET g2_stock_id=NEW.f2_stock_id,g3_stock_name=NEW.f3_stock_name,g4_latest_stock_volume=NEW.f4_latest_stock_volume;
END;
//
DELIMITER ;

お忙しい中ヒントを頂き本当に有難うございました。

補足

しかし、画面上に日付別に一覧を出したときに、いちいちこのselect文を実行するのは処理が遅くなるということであえて、テーブルを作りたいということなら、作ってもいいとは思います。

はい、最終的には購入履歴をグラフ化する予定のため、あえてひとつのテーブルに記録を残したいと考えております。

さて、本題ですが、
修正・削除がない（常に最新の日付で処理を行う。つまり、修正は、差の数量を最新日付で入力。
削除は、-の数量を最新日付で入力）というのなら、now_stockにトリガーを作るほうが簡単。

はい、過去の修正などはありえず、単純に積み重ねだけを考えておりますので、now_stockにトリガーを作るほうが簡単と考えました。

ご指導1
△両方とも、AFTER INSERTにして試してみてください。

【上記のご指導に対する対策として下記を実施してみました】
　両方ともAFTER というだけでなく、A+B A+A B+A B+B の4つ全てをテストし
どのような挙動になるか確認してみました。

【結果】
●a_ref0　　●a_ref000 ●stock_historyのstock_volume
　before before 　 null
　after after 　記録しない
　after before 　 null
　before after 　 記録しない

ご指導2
△なお、INSERT INTO ... ON DUPLICATE KEY UPDATE ... でトリガーを起動しているので
　BEFORE INSERTなら、ON DUPLICATE KEYであろうがなかろうが実行されるようですが、AFTER INSERTは、ON DUPLICATE KEYの時には実行されません。
　したがって、同じ内容で、AFTER UPDATEでもう一つ作る必要があります。
（MySQLは、On Insert,Update・・・がかけない様子。Versionによるのかもしれませんが。）

【上記のご指導に対する対策として下記を実施してみました】

●a_ref0　　●a_ref000
after after

a_ref000は下記に変更

DELIMITER //
CREATE TRIGGER a_ref000 AFTER INSERT ON now_stock
FOR EACH ROW BEGIN
INSERT INTO stock_history SET g2_stock_id=NEW.f2_stock_id,g3_stock_name=NEW.f3_stock_name,g4_latest_stock_volume=NEW.f4_latest_stock_volume
ON DUPLICATE KEY UPDATE g2_stock_id=NEW.f2_stock_id,g3_stock_name=NEW.f3_stock_name,g4_latest_stock_volume=NEW.f4_latest_stock_volume;
END;
//
DELIMITER ;

【結果】
●a_ref0　　●a_ref000
　after 　　　after + duplicate key
●stock_historyのstock_volume→何も記録しない

●a_ref0　　●a_ref000
　before 　　　after + duplicate key
●stock_historyのstock_volume→何も記録しない

「したがって、同じ内容で、AFTER UPDATEでもう一つ作る必要があります。」
というご指導に対するON DUPLICATE KEY UPDATEの追加方法が間違っておりますでしょうか?

△「ON DUPLICATE KEYのほうで呼び出されたときに、２個レコードができてしまうと思いますが。」

ON DUPLICATE KEY UPDATE の作り方が悪かったのか、2個出来ることもなく何も記録されませんでした。

ご指導に対する私の理解力が乏しいのだと思います。
大変お忙しい中、本当に申し訳ございませんが、今一度、ご指導頂けますようお願いいたします。

回答No.1

yambejp

いまいち状況がわからないんですが・・・

buying_stockでかった銘柄ごとの最新データがnow_stockなんですよね？
そもそも最新データを別のテーブルに出す意味はあるのでしょうか？
最新データであれば更新日時もしくは信頼できるauto_incrementされた
idをもとに最新のデータをSQLで探せばいいだけなので・・・

それとbuying_stock自体が履歴になっているような気がするんですが
運用の仕方がちがうのでしょうか？

補足

早速のご指導有難うございます。
やりたいのは、下記のような履歴保管です。
ブラウザ上(PHP上)で計算してbuying_stock に最新保有数量を記入させれば保有数量の履歴まで出来てしまうのは分かっています。
今回考えているものは、ブラウザ上では購入数量の入力のみとし、最新保有数量の計算は、全てサーバー内(trigger)で行いたいと考えております。
どうぞ宜しくお願い致します。

buying_stock　PHP上でユーザーが数量のみ入力
テーブルは購入数量だけを保管
　　　例　 　1/18 +3000株
　　 　　1/19 +5000株
　　 　　1/20 +5000株
　　 　　1/21 -2000株

now_stock　　トリガーで計算
　　テーブルは最新保有数量だけを保管
　　 　　1/21 11000株

stock_history 　トリガーで計算
　保有数量の履歴を保管　←これが出来ません。
　　　例　 　1/18 3000株
　　 　　1/19 8000株
　　 　　1/20 13000株
　　 　　1/21 11000株

以上、宜しくお願い致します。