MySQL トリガーについて

Question

MySQLトリガーについて

トリガーという機能を知らなくて使ってみると便利！
user1というテーブルで行った操作（INSERT/UPDATE/DELETE）を別テーブルにも丸々同じ動作で複製したい時に便利ですね。

それで、下記SQL文の場合だと
user1テーブルの１レコードだけを変更しても
user2テーブルのすべてのレコードが変更対象になってしまうのが問題です。

INSERT INTO user2 (`id`,`surname`,`name`) VALUES (new.id,new.surname,new.name)

WHERE id = old.id

と思いきや違うみたい。
どうすればよろしいでしょうか？

Comments

[退会済みユーザー]

あなたの書いたトリガー文はどこでしょうねぇ

Answer 1

まぁ普通はまるきり同じ複製を作るなんて無駄なことはしませんね
ログをとったり正規化したテーブルの整合性をとったりします
とはいえ、単純にuser1の走査をuser2に適用するならこんな感じ

テーブル作成

CREATE TABLE user1(id INT NOT NULL PRIMARY KEY,surname VARCHAR(30),name VARCHAR(30));
CREATE TABLE user2(id INT NOT NULL PRIMARY KEY,surname VARCHAR(30),name VARCHAR(30));

トリガー作成

DROP TRIGGER IF EXISTS TRG_USER_IN;
DROP TRIGGER IF EXISTS TRG_USER_MOD;
DROP TRIGGER IF EXISTS TRG_USER_DEL;
DELIMITER //
CREATE TRIGGER TRG_USER_IN AFTER INSERT ON user1
FOR EACH ROW BEGIN
INSERT INTO user2(id,surname,name) VALUES(new.id,new.surname,new.name);
END;
//
CREATE TRIGGER TRG_USER_MOD AFTER UPDATE ON user1
FOR EACH ROW BEGIN
UPDATE user2 SET id=new.id,surname=new.surname,name=new.name
WHERE id=old.id;
END;
//
CREATE TRIGGER TRG_USER_DEL BEFORE DELETE ON user1
FOR EACH ROW BEGIN
DELETE FROM user2 WHERE id=old.id;
END;
//
DELIMITER ;

テスト：user1の操作

INSERT INTO user1 VALUES(1,'a','AAA'),(2,'b','BBB'),(3,'c','CCC');
UPDATE user1 SET NAME='XXX' WHERE id=1;
UPDATE user1 SET id=10 WHERE id=2;
DELETE FROM user1 WHERE id=3;

Answer 2

トリガで実行されるSQL文の中にwhere句を書いておかないとテーブルuser2の全件が対象になってしまいます。
トリガの定義のところを

UPDATE user2 SET
surname = new.surname,
name = new.name,
where id = old.id

とすれば期待通りの動作になるのではないでしょうか。

Comments

[earnest_gay]

上記の記述は全く同じものを試しましたが、エラーを吐いてしまいます。

[imutakaoru]

あ。よけいなカンマがありました。失礼しました。

UPDATE user2 SET
surname = new.surname,
name = new.name
where id = old.id

new.nameの後ろのカンマを除去してもダメでしょうか？