TRIGGERの書き方について 再質問

質問

以前質問したものです。解決したと思っていたのですが、エラーが無くなただけで解決はしてませんでしたので再度質問いたします。

MYSQLで株の履歴を作っています。
テーブルとしては、株を購入するユーザー書き込み用のa_nowstock、書き込み履歴を記録するa_nowstock_history、最新の保有株を記録するa_lateststockとなります。

テーブル
a_nowstock　　　　(a1_id(自動取得),a2_user_id,a3_usr_volume,a4_latest_volume)
a_nowstock_history(b1_id(自動取得),b2_user_id,b3_usr_volume)
a_lateststock (d1_id(自動取得),d2_user_id,d3_latest_volume)

a_nowstock はユーザーが書き込むテーブル
a_nowstock_historyは書き込み履歴
a_lateststockは最新保有情報のみのデータとなります。

これをTRIGGERで処理するために下記のようにSQLで指示してみました。

DELIMITER //

CREATE TRIGGER a_ref BEFORE INSERT ON a_nowstock
FOR EACH ROW BEGIN
INSERT INTO a_nowstock_history SET b2_usr_id = NEW.a2_usr_id, b3_usr_volume = NEW.a3_usr_volume;
UPDATE a_lateststock SET d2_usr_id = NEW.a2_usr_id, d3_latest_volume = NEW.a4_latest_volume;
END;
//

DELIMITER ;

これでトリガーを登録すると、エラーは出ないのですが、実際に　a_nowstock に入力してみると、a_nowstock_historyには反映されるものの、a_lateststock　には何も反映されません。

調べてみてもTRIGGERの中でUPDATEする方法が見当たらず途方に暮れています。
詳しい方、大変恐縮ですがアドバイス願います。

回答No.2

Siegrune · ベストアンサー

ANo.1のものですが。
>INSERT INTO a_lateststock SET d2_usr_id = NEW.a2_usr_id, d3_latest_volume = >NEW.a4_latest_volume
>ON DUPLICATE KEY UPDATE d2_usr_id = NEW.a2_usr_id where d2_usr_id = >NEW.a2_usr_id, d3_latest_volume = NEW.a4_latest_volume where d2_usr_id = >NEW.a2_usr_id;

ON DUPLICATE KEY UPDATEの中にwhere句は書けないです。
a2_usr_idをPrimary Keyにするか、a2_usr_id単独でUnique Indexを作成してください。

--前につけたURLより引用--
PRIMARY または UNIQUE キーでの重複値の生成を招くレコードが挿入されると、古いレコードの UPDATE が実行される。次に例を示す。
--引用おわり--
この下を今回の書き方に変えると、
--
mysql> INSERT INTO table set a=1,b=2,c=3
-> ON DUPLICATE KEY UPDATE c=c+1;
このコマンドでは、a が UNIQUE として宣言されていて、かつすでに 1 度、値 1 を保持している場合、次のコマンドと同じになる。

mysql> UPDATE table SET c=c+1 WHERE a=1;
--
という説明になります。

お礼

ご指導有難うございました。
下記で出来ました。
助かりました。心から御礼申し上げます。

a_nowstock　　　　(a1_id(自動取得),a2_user_id,a3_usr_volume,a4_latest_volume)
a_nowstock_history(b1_id(自動取得),b2_user_id,b3_usr_volume)
a_lateststock (d2_user_id(PRIMARY),d3_latest_volume)

DELIMITER //
CREATE TRIGGER a_ref BEFORE INSERT ON a_nowstock
FOR EACH ROW BEGIN
INSERT INTO a_nowstock_history SET b2_usr_id = NEW.a2_usr_id, b3_usr_volume = NEW.a3_usr_volume;
INSERT INTO a_lateststock SET d2_usr_id = NEW.a2_usr_id, d3_latest_volume = NEW.a4_latest_volume
ON DUPLICATE KEY UPDATE d2_usr_id = NEW.a2_usr_id, d3_latest_volume = NEW.a4_latest_volume;
END;
//
DELIMITER ;

回答No.1

Siegrune

a_lateststock にレコードはありますか？

UPDATE a_lateststock SET d2_usr_id = NEW.a2_usr_id, d3_latest_volume = NEW.a4_latest_volume;
を実行しても、レコードがなければ何もしませんが。

updateの処理結果が0件だったらInsertするとか
下のURLでON DUPLICATE KEY UPDATE 節を参照してInsert文にするとか
http://dev.mysql.com/doc/refman/4.1/ja/insert.html
すればレコードがなければ作ってくれます。
（但し、プライマリキーが自動採番じゃ全部Insertしてしまうから設計見直さないと。）

なお、いまの
UPDATE a_lateststock SET d2_usr_id = NEW.a2_usr_id, d3_latest_volume = NEW.a4_latest_volume;
では、a_lateststockに10件レコードがあったら全部同じ内容にしてしまうはずですが。

UPDATE a_lateststock SET d3_latest_volume = NEW.a4_latest_volume
where d2_usr_id = NEW.a2_usr_id;
じゃないのかな？（上の話を反映させるとして。）

補足

有難うございます。

ご指摘にあわせ下記で設定してみたのですが、まだ#1064エラーになります。もう一歩だと思うのですが、お手数ですがご指導願います。

a_nowstock　　　　(a1_id(自動取得),a2_user_id,a3_usr_volume,a4_latest_volume)
a_nowstock_history(b1_id(自動取得),b2_user_id,b3_usr_volume)
a_lateststock (d2_user_id(UNIQUE),d3_latest_volume)

DELIMITER //
CREATE TRIGGER a_ref BEFORE INSERT ON a_nowstock
FOR EACH ROW BEGIN
INSERT INTO a_nowstock_history SET b2_usr_id = NEW.a2_usr_id, b3_usr_volume = NEW.a3_usr_volume;
INSERT INTO a_lateststock SET d2_usr_id = NEW.a2_usr_id, d3_latest_volume = NEW.a4_latest_volume
ON DUPLICATE KEY UPDATE d2_usr_id = NEW.a2_usr_id where d2_usr_id = NEW.a2_usr_id, d3_latest_volume = NEW.a4_latest_volume where d2_usr_id = NEW.a2_usr_id;
END;
//
DELIMITER ;

エラー
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DUPLICATE KEY UPDATE d2_usr_id = NEW.a2_usr_id where d2_usr_id = NEW.a2_usr_id, ' at line 6