yoku0825さま

ありがとうございます。

今少し前に、自分自身以外のテーブルの値を更新するもののみにしたら、意図通りに動きました。
TRIGGERはそのように使うものと理解します。

レコードの更新タイムスタンプを自動的に記録する方法をお教えいただき、ありがとうございました。

お忙しいところ、ありがとうございました。



On 2016/07/27 15:37, yoku ts. wrote:
> yoku0825といいます。
>
>
>> もしこれが原因とすると、レコードの更新タイムスタンプを自動的に記録するにはどのようにするのが一般的なのでしょうか？
> 5.6で、lastupdtimeがDATETIME型なら、ON UPDATE CURRENT_TIMESTAMP属性を使うことができます。
> ALTER TABLE child_table MODIFY lastupdtime DATETIME (必要ならNOT NULL) ON
> UPDATE CURRENT_TIMESTAMP;
> を実行すると、lastupdtimeカラムは更新がかかるたびにその時刻のタイムスタンプに更新されます。
>
> http://dev.mysql.com/doc/refman/5.6/ja/timestamp-initialization.html
>
> ただし、dataカラム以外が更新された場合でもlastupdtimeの更新は走ります。
> 個人的には、UPDATEステートメントを叩くアプリケーションの箇所で `lastupdtime = sysdate()`
> も入れてやるのが良い方が気がしますが。。
>
>
> yoku0825,
>
>
> 2016年7月27日 15:29 ochiai <s.ochiai@xxxxxxxxxx>:
>> yoku0825さま
>>
>> ありがとうございます。
>> 色々とやっているうちに、混乱してきてしまいました。ただ、yoku0825さまのコメント
>> 「というわけで、「複数ステートメントのトリガーが定義できない」ではなく、「自分自身を呼び出してしまうようなトリガーなのでUPDATEステートメントが転けた」のような気がします。」を読んで、私が寄ろうとしていた処理のおかしい点に思い当りました。
>>
>> BEGIN
>> ＃childテーブルの内容が更新されたので、parentテーブルのタイムスタンプを更新する。
>> update parent_table set lastupdtime = sysdate() where id = old.p_id;
>> ＃childテーブルの内容が更新されたので、当該レコードのタイムスタンプを更新する。
>> update child_table set lastupdtime = sysdate() where new.data <> old.data;
>> END;
>>
>> つまり、自分自身のレコードのタイムスタンプを更新時点の値に更新し、親のレコードのタイムスタンプも更新したいのです。
>> しかし、更新後に自分自身のレコードのタイムスタンプをさらに更新すれば、updateイベントが発生するので、無限ループになってしまう。
>> これが原因かなと思いました。
>>
>> もしこれが原因とすると、レコードの更新タイムスタンプを自動的に記録するにはどのようにするのが一般的なのでしょうか？
>> before_updateで自分自身のレコードのタイムスタンプを更新するのでしょうか？
>> 皆さんはどうされてますか？
>>
>>
>> On 2016/07/27 12:00, yoku ts. wrote:
>>> こんにちは、yoku0825といいます。
>>>
>>> なんかいくつか混じっているのに気が付きました！
>>>
>>>>> トリガーの対象とした テーブル の行を更新しようとすると下記のエラーが発生し、更新ができません。
>>>> Error Code: 1235. This version of MySQL doesn't yet support 'multiple
>>>> triggers with the same action time and event for one table'    0.016 sec
>>> これは `CREATE TRIGGER` の時にしか出ないはずです（UPDATEやINSERTではこのエラーメッセージに到達しないはず）
>>> 更新しようとした時のエラーをもう一度確認してもらえますか？
>>>
>>>
>>>>> show triggersの結果もDELIMITERが反映されていません。
>>> DELIMITERは複数ステートメントの構文（BEGIN .. END）をセットする時に便宜的に利用するキーワードなので、
>>> `SHOW TRIGGERS` や `SHOW CREATE TRIGGER` には出力されません。
>>> BEGIN .. ENDでちゃんと囲われているので、両方のステートメントが動くはずなのですが。
>>> （そしてエラーになるなら、たとえばカラムの名前が違う、とか、少なくとも1235でないエラーコードだと思うのですが）
>>>
>>>
>>>> また、BEGIN ... ENDのENDに";"ではなく、"$$"”を付けましたが同じエラーでした。
>>> これは、トリガー作成時のエラーですよね？ （これはエラー1235のはずです）
>>>
>>>
>>> 1. 以下の構文自体は合っています。 `DROP TRIGGER child_table_AFTER_UPDATE`
>>> してからもう一度流すと作成されるはずです。
>>> 　 （エラー1235が返るなら、他の名前でAFTER UPDATEトリガーが既に存在しています）
>>>
>>> ```
>>> DELIMITER $$
>>>
>>> CREATE DEFINER=`root`@`localhost` TRIGGER `child_table_AFTER_UPDATE`
>>> AFTER UPDATE ON `child_table` FOR EACH ROW
>>> BEGIN
>>> update parent_table set lastupdtime = sysdate() where id = old.p_id;
>>> update child_table set lastupdtime = sysdate() where new.data <> old.data;
>>> END;
>>> $$
>>> ```
>>>
>>> 2. show triggers;のstatementの値は以下で合っています（以下になれば意図したトリガーの作成に成功している）
>>>
>>> ```
>>> BEGIN
>>> update parent_table set lastupdtime = sysdate() where id = old.p_id;
>>> update child_table set lastupdtime = sysdate() where new.data = old.data;
>>> END;
>>> ```
>>>
>>> 3. トリガーの作成時にエラーになったのか、トリガーの作成後にUPDATEしたタイミングでエラーになったのかもう一度確認してみてください。
>>>
>>> 4. トリガーの定義自体はエラーにならずできました。5.7.13 on Linux, 5.6.31 on Linux, 5.6.31 on
>>> Windowsの3環境で試しています（クライアントはMySQL Workbench 6.3 on Windowsです）
>>> 4-1. スキーマがよくわからなかったので、文中に登場したカラムと外部キーだけ雑に貼りました
>>> https://gist.github.com/yoku0825/83d723a13b532caf6314496fe72e9007
>>> 　　mysqldump --triggers でトリガーも出力されています。
>>> 4-2.
>>> UPDATEしようとしたらエラーになりましたが、1235ではありませんでした。これはたぶん、トリガーの中でUPDATEしちゃうとトリガーが無限ループしちゃうからだと思います。
>>>
>>> （UPDATEトリガーの中でchild_tableをUPDATEしてUPDATEトリガーが呼ばれてその中でchild_tableをUPDATEしてまたUPDATEトリガーが…）
>>>
>>> ```
>>> Error Code: 1442. Can't update table 'child_table' in stored
>>> function/trigger because it is already used by statement which invoked
>>> this stored function/trigger.
>>> ```
>>>
>>>
>>>
>>> というわけで、「複数ステートメントのトリガーが定義できない」ではなく、「自分自身を呼び出してしまうようなトリガーなのでUPDATEステートメントが転けた」のような気がします。
>>>
>>>
>>> yoku0825,
>>>
>>> 2016年7月27日 11:19 ochiai <s.ochiai@xxxxxxxxxx>:
>>>> yoku0825さん、ありがとうございます。
>>>>
>>>> On 2016/07/22 12:31, yoku ts. wrote:
>>>>
>>>>> こんにちは、yoku0825といいます。
>>>>>
>>>>> CREATE TRIGGERの文法は合っているような気がします。
>>>>>
>>>>>> Error Code: 1235. This version of MySQL doesn't yet support 'multiple
>>>>>> triggers with the same action time and event for one table'    0.016
>>>>>> sec
>>>>> そのテーブルには既にAFTER UPDATEトリガーが設定されています。
>>>>> それが予期したものでない（今設定されているAFTER UPDATEトリガーは何度かやっているうちに残っちゃった）ものであれば、
>>>>> DROP TRIGGERで消してあげてください。
>>>>
>>>> AFTER UPDATEトリガーをドロップしてから、show triggers;でドロップされてい るのを確認し、
>>>> CREATE TRIGGER し、show triggers;で定義されているのを確認しています。
>>>> ですから、何度かやっているうちに残っちゃったという状況ではないでしょう。
>>>>
>>>>> 現在設定されているAFTER UPDATEトリガーが消せないものの場合、
>>>>>
>>>>> 1) MySQL 5.7はAFTER UPDATEトリガーを複数セットできます
>>>> MySQL 5.6 リファレンスマニュアル/13.1.19 CREATE TRIGGER 構文 に以下のよ うな記述がありますので、MySQL
>>>> 5.6でもできるのかなと思ったのです。
>>>>
>>>> 「trigger_body は、トリガーがアクティブ化されるときに実行されるステート メントです。複数のステートメントを実行するには、BEGIN
>>>> ...
>>>> END 複合ステー トメント構造構文を使用します。また、これにより、ストアドルーチン内で許可
>>>> されるのと同じステートメントを使用することもできます。セクショ ン13.6.1 「BEGIN ... END
>>>> 複合ステートメント構文」を参照してください。一部のステー トメントは、トリガー内では許可されません。セクションD.1「ストアドプログ
>>>> ラムの制約」を
>>>> 参照してください。」
>>>> http://dev.mysql.com/doc/refman/5.6/ja/create-trigger.html
>>>>
>>>> ちなみに５．５のマニュアル（英文）も調べたところ、同じようなことが書いて ありました。
>>>> http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html
>>>>
>>>> また、BEGIN ... ENDのENDに";"ではなく、"$$"”を付けましたが同じエラーでした。
>>>>
>>>> 5.7でできましたか？
>>>> MySQLのバージョン以外の問題ということはないですか？
>>>> 例えば、unix版はできるが、windows版はできないとか。
>>>>
>>>> 私がインストールしたMySQLは下記のインストーラーでインストールしたものです。
>>>> mysql-installer-community-5.6.31.0.msi
>>>>> 2) 今あるAFTER UPDATEトリガーの中に新しいトリガーのステートメントを混ぜます
>>>> それができないのです。
>>>>> 3) 新しい方をBEFORE UPDATEトリガーで代用できないか検討します
>>>> 根本的な解決ではなさそうです。
>>>>
>>>>> のうちどれを選ぶことになるかと思います。
>>>>>
>>>>>
>>>>> yoku0825,
>>>>>
>>>>> 2016年7月22日 12:16 ochiai<s.ochiai@xxxxxxxxxx>:
>>>>>> MLの皆様、お世話になります。
>>>>>>
>>>>>> CREATE TRIGGERで困っています。
>>>>>> やりたいことは、親テーブルとその明細のテーブルがあり、明細テーブルのいず れかの行の値(data)を更新したら、更新のタイムスタンプを親
>>>>>> テーブルと子
>>>>>> テーブルのそれぞれに記録するということです。
>>>>>> よろしくお願いします。
>>>>>>
>>>>>> 一つのイベントに対して複数のSQLを実行するTRIGGERを定義したいのですが、う まくできません。
>>>>>> 調べてみたら、複数のサイトにCREATE TRIGGER文をDELIMITER句で挟めば複数の
>>>>>> SQL文を定義できる、と書いてありましたので、その通りにやりましたがうまく いきません。
>>>>>>
>>>>>> １）create triggerを delimiter $$　〜　$$ delimiter;でくくる
>>>>>> ２）複数のSQL文をBEGIN　〜　ENDでくくる
>>>>>> 参考にしたサイト：http://wikiwiki.jp/yonkoushi2/?MySQL%2Fdbonline%2Fview
>>>>>>
>>>>>> 以下のようにMySQL Workbench(6.3Community)のSQLエディターで下記のSQLを実
>>>>>> 行し、エラーなくトリガーが作成されますが、トリガーの対象とした テーブル の行を更新しようとすると下記のエラーが発生し、更新ができません。
>>>>>> なお、親テーブルと明細テーブルとの連携キーは parent_table.id=child_table.p_idです。
>>>>>>
>>>>>> ＜トリガー作成＞
>>>>>> DELIMITER $$
>>>>>>
>>>>>> CREATE DEFINER=`root`@`localhost` TRIGGER `child_table_AFTER_UPDATE`
>>>>>> AFTER
>>>>>> UPDATE ON `child_table` FOR EACH ROW
>>>>>> BEGIN
>>>>>> update parent_table set lastupdtime = sysdate() where id = old.p_id;
>>>>>> update child_table set lastupdtime = sysdate() where new.data <>
>>>>>> old.data;
>>>>>> END;
>>>>>> $$
>>>>>>
>>>>>> DELIMITER ;
>>>>>>
>>>>>> ＜エラーメッセージ＞
>>>>>> CREATE DEFINER=`root`@`localhost` TRIGGER `child_table_AFTER_UPDATE`
>>>>>> AFTER
>>>>>> UPDATE ON `child_table` FOR EACH ROW
>>>>>> BEGIN
>>>>>> update parent_table set lastupdtime = sysdate() where id = old.p_id;
>>>>>> update child_table set lastupdtime = sysdate() where new.data =
>>>>>> old.data;
>>>>>> END;
>>>>>> Error Code: 1235. This version of MySQL doesn't yet support 'multiple
>>>>>> triggers with the same action time and event for one table'    0.016
>>>>>> sec
>>>>>>
>>>>>> ＜show triggers;のstatementの値＞
>>>>>> BEGIN
>>>>>> update parent_table set lastupdtime = sysdate() where id = old.p_id;
>>>>>> update child_table set lastupdtime = sysdate() where new.data =
>>>>>> old.data;
>>>>>> END;
>>>>>>
>>>>>> エラーメッセージもshow triggersの結果もDELIMITERが反映されていません。
>>>>>> エラーメッセージに“This version of MySQL doesn't yet support 'multiple
>>>>>> triggers...”とあるので、5.6.31では対応していないということでしょうか？対 策あるいは対応しているバージョンを教えてください。
>>>>>>
>>>>>>
>>>>>> 当方の実行環境：
>>>>>> MySQL version:5.6.31-log MySQL Community Server(GPL)
>>>>>>          Compiled For: win32(AMD64)
>>>>>>
>>>>>> MySQL Workbench(6.3Community)
>>>>>>
>>>>>> OS:Windows 7 Professional 32bit
>>>>>>
>>>>>>
>>